Role-based Access Control In SQL Server, and You

Role-Based Access Control (RBAC) is a security model where permissions are associated with roles, and users are assigned to these roles, thus gaining the role’s permissions. In SQL Server, there are server-level roles and database-level roles. Server-level roles have permissions that are applicable across the entire instance, while database-level roles have permissions that are specific to a particular database. DBAs can also create user-defined roles at both the server and database levels, providing a flexible and granular approach to managing access control.

RBAC is often considered a superior security model due to its flexibility and manageability. For instance, consider a traditional access control model where permissions are assigned directly to each user. In a large organization, this can quickly become unmanageable. Each time a user changes roles or leaves the organization, their permissions need to be individually updated, which is time-consuming and error-prone.

In contrast, with RBAC, permissions are assigned to roles, not users. Users are then assigned roles, thereby inheriting the permissions of those roles. If a user changes roles or leaves the organization, you simply update the user’s role assignment, and their permissions are automatically updated.

Also, RBAC aligns well with the principle of least privilege, which states that users should be given the minimum permissions necessary to perform their tasks. By grouping permissions into roles, it’s easier to ensure that users only have access to the resources they need, enhancing the security of your system.

Here is a quick real-life example:

In a hospital, there are various roles such as doctors, nurses, administrators, and IT staff, each requiring different access to the hospital’s database. For instance, doctors need access to patient medical records, nurses need access to medication schedules, administrators need access to billing information, and IT staff need access to maintain the system.

Without RBAC, each time a new employee joins, leaves, or changes roles within the hospital, their permissions would need to be individually updated. This could be a complex and error-prone process given the size of the hospital and the number of systems they need to access.

With RBAC, permissions are grouped into roles that correspond to the job functions (doctor, nurse, administrator, IT staff). When a new employee joins, they are simply assigned the role that corresponds to their job function, automatically granting them the necessary permissions. If an employee changes roles or leaves the hospital, their role assignment is updated, automatically updating their permissions.

This simplifies permission management, reduces errors, and enhances security by ensuring that employees only have access to the information they need to perform their job.

There are a few downsides, however.

Using roles in combination with something like domain groups, which is the RBAC of the Active Directory world, means there would need to be a great deal of oversight. If you have a domain group called something like ‘SQL sysadmins’ and it has the related sql role of ‘sysadmin’, any person that is added to the group will gain those permissions. As you may be able to see, the risk for using domain groups with RBAC on the SQL Side is that once the permissions are set and the groups granted, there needs to be an extremely close relationship and auditing of who can even add users to those domain groups. There are many ways to work through this issue, but it is ultimately up to each company how they wish to address it. Some choose traditional access control to avoid this exact situation.

Another risk is in nesting roles within roles, which may lead to a phenomenon known as “privilege creep” or “runaway privileges”. This occurs when a user is granted a role that contains other roles, thereby inadvertently gaining access to a broader set of permissions than intended. Over time, this can result in users accumulating more privileges than they need to perform their tasks, violating the principle of least privilege. This not only poses a security risk but also makes privilege management complex and difficult to track. It becomes challenging to determine who has access to what, as the permissions a user has are not directly assigned but inherited through multiple levels of roles. Therefore, while role nesting can be useful for organizing permissions, it should be used judiciously, with a clear understanding of the implications for access control and privilege management.

The script below helps to identify the current role membership on both the server and database level, as well as provides a script to remove a member from the role they are in. As always, please make sure you fully understand the danger in making any changes before doing so, and test in a sandbox environment first. A lot of damage can be done if you are not careful.

--Find Server-Level Role Memberships 
SELECT DP1.name AS ServerRoleName
,	isnull(DP2.name, 'No members') AS ServerUserName 
, DP2.type_desc
, 'EXEC sp_dropsrvrolemember ['+DP2.name+'], ['+DP1.name+'];' AS [Role Removal Script - Be EXTREMELY careful]

FROM sys.server_role_members AS DRM  
RIGHT OUTER JOIN sys.server_principals DP1  
	ON DRM.role_principal_id = DP1.principal_id  
LEFT OUTER JOIN sys.server_principals  DP2  
	ON DRM.member_principal_id = DP2.principal_id  
WHERE DP1.type = 'R' 
AND DP2.name NOT LIKE '%NT SER%'
AND DP2.name <>'sa'
ORDER BY DP1.name;

-- Find Database-Level Role Memberships 
	IF OBJECT_ID('tempdb..#dbroles') IS NOT NULL
        DROP TABLE #dbroles

    CREATE TABLE #dbroles (
        [Database Name] NVARCHAR(100)
        , DatabaseRoleName NVARCHAR(100)
		, ServerUserName NVARCHAR(100)
		, type_desc NVARCHAR(30)
		, [Role Removal Script - Be EXTREMELY careful] NVARCHAR(500)
        )

Insert #dbroles
EXEC sp_MSforeachdb 'USE ? 
SELECT ''?'' AS [Database name]
,	DP1.name AS DatabaseRoleName
,	isnull(DP2.name, ''No members'') AS ServerUserName   
,  DP2.type_desc
, ''USE ''+ ''?'' + ''; EXEC sp_droprolemember [''+DP2.name+''], [''+DP1.name+''];  '' AS [Role Removal Script - Be EXTREMELY careful]
FROM sys.database_role_members AS DRM  
RIGHT OUTER JOIN sys.database_principals DP1  
	ON DRM.role_principal_id = DP1.principal_id  
LEFT OUTER JOIN sys.database_principals DP2  
	ON DRM.member_principal_id = DP2.principal_id  
WHERE DP1.type = ''R'' 
AND DP2.name NOT LIKE ''%##%''
AND DP2.name NOT LIKE ''%dbo%''
AND DP2.name NOT LIKE ''%dc_%''
AND DP2.name NOT LIKE ''%MS_%''
AND DP2.name NOT LIKE ''%Role%''
ORDER BY DP1.name; '

select * from #dbroles where [Database Name] not in ('msdb','master','model')