SQL Server Blog

Role-based Access Control In SQL Server, and You

This post is part of our SQL Server security blog series, 30 SQL Server Security Checks in 30 Days. We’re publishing a new security check every day in the month of June. Visit our sp_CheckSecurity page to learn about our free SQL Server tool you can download and run to check your own server.

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 <>'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')
David Seis
Article by David Seis
David Seis is a Support Technician that joined Straight Path in the summer of 2021. His goal is to work in an area that he believes helps others, and to do it to the best of his ability. He loves to help, solve problems, and make things easy for others to understand. Because of this he is lovingly known as a “Good helper” among friends and family. Outside of work, he has a growing passion for cybersecurity, specifically helping protect those who are most vulnerable to fraud, scams, and phishing. He looks forward to learning more about each of his clients, helping minimize issues, and ensure that any problems are solved quickly.

Subscribe for Updates


Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share This