How to implement and manage role-based access control

Implementing Role-Based Access Control (RBAC) in SQL Server lets you control access to database resources and manage user permissions, ensuring compliance with the principle of least privilege.

This topic provides techniques with practical examples for implementing and managing role-based access control (RBAC) in SQL Server using the dbForge Studio for SQL Server tools, such as Security Manager, SQL Editor, Schema Compare, Unit Test, and AI Assistant.

Prerequisites

• A SQL Server instance with administrative access to create users, roles, and permissions.
• A target database to apply RBAC.

Step 1: Understand your security requirements

Before implementing RBAC, define user groups and required permissions based on job functions:

• Identify a list of application or user roles, for example, Admin, Developer, Analyst, or users with read-only access.
• Identify database objects that each role must have access to, for example, tables, views, or stored procedures.
• Specify required permissions for each role and object, for example, SELECT, INSERT, UPDATE, and EXECUTE.
• Assign to the sysadmin fixed server role, or provide sufficient privileges to create logins, users, and roles.
• Define the ALTER ANY LOGIN, ALTER ANY USER, and ALTER ANY ROLE permissions in the relevant databases.
• Use the dbForge Studio Documentation feature to create a security plan for reference.

Tip

Always follow the principle of least privilege when assigning permissions. Grant each role or user only the specific privileges required to perform their tasks. Avoid broad roles, such as sysadmin, unless absolutely necessary.

Step 2: Create SQL Server logins and database users

A login provides server-level authentication, while a database user maps that login to a particular database.

You can create logins and users by using Security Manager or SQL Editor.

To create logins and database users using Security Manager:

1. Open Security Manager.

2. Create a new login:

• Specify the login name.
• Select an authentication type, for example, SQL Server Authentication or Windows Authentication.
• Specify the password if applicable.

3. On the User Mapping tab, map the login to the target database, assign a database user name and default schema.

Note

If no user exists, create a new one before proceeding.

4. Save the changes.

To create logins and users using SQL Editor, open SQL Editor and execute the query to create a login and user.

USE <database_name>;
GO

CREATE USER [user_name] FOR LOGIN [login_name];

where:

• <database_name> – The database for which you want to create a login.
• user_name – The username to be created.
• login_name – The login for the username.

Warning

If the statement fails with a permissions error, grant the user or role the minimal required permissions, then rerun the statement.

USE <database_name>;
GO

GRANT SELECT, INSERT, UPDATE, DELETE ON OBJECT::<schema_name>.<table_name> TO [user_name];

Tip

Use Windows Authentication for integrated security whenever possible to simplify credential management and enhance security.

Step 3: Create database roles

You can use database roles to group permissions, making them easier to manage and assign to users. To create custom database roles, use Security Manager or SQL Editor.

To create a database role using Security Manager:

1. Open Security Manager.

2. Create the role, for example, Role_Analysts, and assign a schema owner if needed.

3. Save the changes.

4. On the Owned Schema tab, select db_datareader.

Note

The role must have permission to read the database.

5. Save the changes.

6. Repeat these steps for each role you want to create.

Tip

Use descriptive role names that clearly reflect their purpose. For example, use Role_Finance_ReadOnly for finance team members who require read-only access.

To create a database role using the CREATE ROLE statement, open SQL Editor and execute the query to create a database role.

USE <database_name>;

CREATE ROLE <role_name>;

where:

• <database_name> – The database for which you create a database role.
• <role_name> – The name of the role you want to create.

Step 4: Grant permissions to roles

You can grant specific permissions to roles to control access to database objects. To grant permissions to roles, use Security Manager or SQL Editor.

To grant permissions using Security Manager:

1. Open Security Manager.

2. Select the role to which you want to grant permissions, for example, Role_Analyst.

3. On the Object Permissions tab, select the required database objects, for example, tables, views, or stored procedures.

4. In Available privileges, assign permissions, for example:

• SELECT for read-only access.
• INSERT and UPDATE for data modification.
• EXECUTE for stored procedures.

5. Select Grant.

6. Save the changes.

To grant permissions using SQL Editor, open SQL Editor and execute the query to grant permissions.

GRANT SELECT ON <table_name> TO <role_name>; -- Gives the role permission to read data from the specified table.

GRANT EXECUTE ON <stored_procedure_name> TO <role_name>; -- Grants the role permission to execute the specified stored procedure.

GRANT INSERT, UPDATE ON <table_name> TO <role_name>; -- Grants the role specific permissions to add new rows (INSERT) and modify existing rows (UPDATE) in the specified table.

where:

• <table_name> – The name of the table from which you want the role to read or manage data.
• <stored_procedure_name> – The name of the stored procedure.
• <role_name> – The name of the role to which you want to grant permissions.

Tip

It is recommended to grant permissions at the schema level for broader access control.

GRANT SELECT ON SCHEMA::<schema_name> TO <role_name>;

where:

• <schema_name> – The name of the schema to which permissions are granted.
• <role_name> – The name of the role to which permissions are granted at the schema level.

Step 5: Deny permissions

If you want to restrict access, you can deny specific permissions using the DENY statement in SQL Editor.

DENY DELETE ON dbo.Sales TO Role_Analysts;

Alternatively, select the checkbox in the Deny column for a specific permission in Security Manager.

Step 6: Assign users to roles

You should map database users to roles to grant them the associated permissions.

You can assign users to roles by using Security Manager or SQL Editor.

To assign users to roles using Security Manager:

1. Open Security Manager.

2. Select the user for the target database.

3. On the Role Membership tab, select the role.

4. Save the changes.

5. Repeat these steps for each user you want to assign the role.

To assign users to roles using SQL Editor, open SQL Editor and execute the query to assign a user to roles.

USE <database_name>; 

ALTER ROLE <role_name> ADD MEMBER <user_name>;

where:

• <database_name> – The database on which you want to execute the statement.
• <role_name> – The name of the role that you want to assign to the user.
• <user_name> – The name of the user to which the role will be assigned.

Tip

Use Windows groups as logins, for example, DOMAIN\FinanceTeam, and map them to roles to simplify user management for large teams.

Step 7: Test role-based access

After you assign permissions, verify that users have the appropriate access and can’t access unauthorized resources.

You can verify permissions by using SQL Editor:

1. In dbForge Studio for SQL Server, establish a new connection by specifying the user credentials you’ve created and database.

2. Open SQL Editor.

3. Attempt unauthorized actions.

For example, create a role in the database. Since the user doesn’t have permission to create roles, the error occurs.

4. Adjust the permissions in Security Manager and try to create a role.

Tip

Use dbForge Studio Unit Test feature to automate permission testing for critical database operations.

Step 8: Monitor and audit role-based access

It is recommended to regularly audit roles and permissions to ensure compliance and detect security issues. To monitor and audit access, use Security Manager or SQL Editor, or by enabling SQL Server Audit (if supported by your edition).

Use Security Manager to monitor and audit role-based access

In Security Manager, verify the assigned permissions for each role and user.

Use SQL Editor to monitor and audit role-based access

Execute the system views for auditing to view permissions for each role.

SELECT
  dp.name AS [UserName]
 ,dp.type_desc AS [UserType]
 ,dp.authentication_type_desc AS [AuthType]
 ,dp.create_date
 ,dp.modify_date
 ,rp.name AS [RoleName]
FROM sys.database_principals dp
LEFT JOIN sys.database_role_members drm
  ON dp.principal_id = drm.member_principal_id
LEFT JOIN sys.database_principals rp
  ON drm.role_principal_id = rp.principal_id
WHERE dp.type NOT IN ('A', 'G', 'R', 'X')
AND dp.sid IS NOT NULL
ORDER BY dp.name, rp.name;

Enable SQL Server audit to track login and permission changes

1. In SQL Editor, create a database with a table to display logs, and a trigger that will log all entries on the server.

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'AdminLogs')
    CREATE DATABASE AdminLogs;
GO

USE AdminLogs;
GO

CREATE TABLE dbo.UserLogins (
    LogID INT IDENTITY PRIMARY KEY,
    LoginName SYSNAME,
    HostName NVARCHAR(255),
    AppName NVARCHAR(255),
    LoginTime DATETIME DEFAULT GETDATE(),
    ClientIP NVARCHAR(64)
);
GO

USE master;
GO

CREATE TRIGGER trg_LogUserConnection
ON ALL SERVER
FOR LOGON
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Login SYSNAME = ORIGINAL_LOGIN(),
            @Host NVARCHAR(255) = HOST_NAME(),
            @App NVARCHAR(255) = APP_NAME(),
            @IP NVARCHAR(64) = (SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID);

    INSERT INTO AdminLogs.dbo.UserLogins (LoginName, HostName, AppName, ClientIP)
    VALUES (@Login, @Host, @App, @IP);
END;
GO

2. Execute the SELECT query to view all logons on the server.

SELECT * FROM dbo.UserLogins ORDER BY LoginTime DESC;

where:

• dbo.UserLogins – The user-defined table from which the data is retrieved.
• LoginTime – The column by which data is sorted in descending order, meaning the most recent logins appear first.

This query returns all user login records, showing the latest logins at the top of the result set.

Generate audit reports

You can use dbForge Studio Report Designer for compliance reviews and audit reports.

Schedule periodic audits

You can schedule periodic audits using SQL Server Agent to run audit queries and export results in a report.

Tip

Use dbForge Studio AI Assistant to generate a query that identifies users or database principals granted the DELETE permission.

Step 9: Maintain and update RBAC policies

It is recommended to regularly update roles and permissions to reflect changes in user responsibilities or database structure.

You can manage changes by using:

• Security Manager to modify roles to grant or revoke permissions as needed.

• Schema Compare to identify schema changes and update role permissions accordingly.

• SQL Editor to remove obsolete users or roles.

USE <database_name>;

DROP USER <user_name>;
DROP ROLE <role_name>;

where:

• <database_name> – The database name from which you want to drop the user and role.
• <user_name> – The username you want to drop.

• <role_name> – The name of the role you want to drop.

• dbForge Studio Documentation feature to document changes for compliance and team collaboration.

• Schedule scripts in SQL Server Agent to automate permission reviews.

Tip

Use version control for RBAC scripts and document changes with dbForge DevOps Automation in a CI/CD pipeline.

Recommendations

In addition to using dbForge Studio for SQL Server tools, such as Security Manager, SQL Editor, Schema Compare, Unit Test, and AI Assistant, you can follow these recommendations to effectively implement and manage role-based access control, ensuring a secure and compliant SQL Server environment.