SQL Server Database Users to Roles Mapping Report

By:   |   Updated: 2022-03-25   |   Comments (22)   |   Related: > Security


Problem

Using SQL Server database roles, in my opinion, is the simplest security method to assign and manage user permissions. I think this is the most common method that Database Administrators (DBA) use to handle permissions using either fixed database roles or creating user-defined database roles. This comes from over two decades of doing SQL database administration work.

Traditionally SQL Server provides two types of database-level roles: fixed-database roles that are predefined in the database and user-defined database roles that you can create. The database roles are defined at the database level and exist on each database. When the DBA maps the logins to databases, he/she can also create members of these database roles that manage the security in the database.

To help illustrate the fixed database roles, below is an image that explains them (this is from Microsoft MSDN, https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-2017).

SQL Server Database Level Roles and Permissions

In SQL Server Management Studio (SSMS), when you click the user mapping tab, you can assign any database role in the database to a user, but you cannot see in a single screen all of the database roles assigned to each database user. In order to do that you must click on every user line and "collect" the database roles assigned to each user. Therefore, there is a need for a simple T-SQL tool that generates a "database users to database roles mapping", this tool should be able to map all the database roles or to focus on a single role.

Solution

My solution involves creating a stored procedure that generates a users to database roles mapping. This stored procedure can be created in the master database and then called from any database.

The stored procedure gets one parameter containing the database role name. If the report is called to retrieve all database role mappings then it defaults to a value of '%' (meaning all values).

This stored procedure (dbo.sp_dbRolesUsersMap) joins to (according to the system relationship model) three system tables:

sys.database_role_members This system table returns one row on each member of each database role. Database users, application roles, and other database roles can be members of a database role
sys.database_principals This system table returns a row for each security principal in a SQL Server database
sys.server_principals This system table Contains a row for every server-level principal

The stored procedure distinguishes between three main user types: SQL user, Windows user and Windows group. It focuses only on database user roles and filters out system and INFORMATION_SCHEMA roles from the result.

The stored procedure shows the following columns:

  1. The user type (Windows group / Windows user/ SQL user)
  2. The database user name
  3. The server login name associated with that user
  4. The assigned database role name

SQL Server Stored Procedure to Show Users to Database Roles Mapping

USE master
GO

-- ================================================ 
-- Author:      Eli Leiba         
-- Create date: 24-03-2019            
-- Description: Simple Users to Database Roles mappings           
-- ================================================
CREATE PROCEDURE dbo.sp_dbRolesUsersMap (@dbRole SYSNAME = '%')
AS
SELECT 
    DB_NAME() as DB_Name,
    User_Type = 
    CASE mmbrp.[type] 
    WHEN 'G' THEN 'Windows Group' 
    WHEN 'S' THEN 'SQL User' 
    WHEN 'U' THEN 'Windows User' 
    END,
    Database_User_Name = mmbrp.[name],
    Login_Name = ul.[name],
    DB_Role = rolp.[name]
FROM 
    sys.database_role_members mmbr, -- The Role OR members associations table
    sys.database_principals rolp,     -- The DB Roles names table
    sys.database_principals mmbrp,    -- The Role members table (database users)
    sys.server_principals ul          -- The Login accounts table
WHERE 
    Upper(mmbrp.[type]) IN ( 'S', 'U', 'G' )
    -- No need for these system account types
    AND Upper (mmbrp.[name]) NOT IN ('SYS','INFORMATION_SCHEMA')
    AND rolp.[principal_id] = mmbr.[role_principal_id]
    AND mmbrp.[principal_id] = mmbr.[member_principal_id]
    AND ul.[sid] = mmbrp.[sid]
    AND rolp.[name] LIKE '%' + @dbRole + '%'
GO

Mark as a system stored procedure so it can be called from any database.

USE master
GO

EXEC sp_ms_marksystemobject 'sp_dbRolesUsersMap' 
GO 

Sample Execution

Generate the users to database roles mapping for the entire NorthwindTrainingDB database.

USE NorthwindTrainingDB
GO

exec sp_dbRolesUsersMap
GO

Here are the results (on my server):

Sample Execution with the SQL Server In-Line Table Function

Generate only the users to database roles mapped to the db_ddlAdmin role in the NorthwindTrainingDB database.

USE NorthwindTrainingDB
GO

exec sp_dbRolesUsersMap 'db_ddlAdmin'
GO			

Here are the results (on my server):

Results for DB-DDLAdmin

Get Users, Logins and Roles for All Databases

Here is a way to use the above to find information for all databases.

CREATE TABLE #temp 
  ( dbname nvarchar(100)
   ,usertype nvarchar(100)
   ,username nvarchar(100)
   ,loginname nvarchar(100)
   ,dbrole nvarchar(100)
  )

EXEC sp_MSforeachdb 'USE ? insert into #temp exec sp_dbRolesUsersMap' 

SELECT * FROM #temp
Next Steps
  • You can create and compile this simple stored procedure and use it as a simple tool to generate a fast database user to database roles reports.
  • Note that the stored procedure is not limited to only the fix database roles. If user defined database roles exist and map to a user then it will show on the report, but its inner permission will be absent from the report.
  • The code uses general SQL Server tables and functions so it should be compatible with SQL Server 2008 and above. The security system objects: sys.server_principals, sys.database_principals and sys.database_role_members were all introduced starting with the SQL Server 2008.



Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips


Article Last Updated: 2022-03-25

Comments For This Article




Wednesday, June 8, 2022 - 1:30:41 PM - Ellie Back To Top (90148)
Works great for me! Thank you very much!

Monday, March 28, 2022 - 4:38:42 PM - Alex Back To Top (89942)
Improved version of last code is this which I used after I change to offline a database having the blank in the name and it failed on the next database because canot not see after space (blank) and reports the databse as not existing...

Better script:

IF OBJECT_ID(N'tempdb..#temp') IS NOT NULL
BEGIN
DROP TABLE #temp
END
GO

CREATE TABLE #temp
( dbname nvarchar(100)
,usertype nvarchar(100)
,username nvarchar(100)
,loginname nvarchar(100)
,dbrole nvarchar(100)
)

EXEC sp_MSforeachdb 'USE ? insert into #temp exec sp_dbRolesUsersMap'

SELECT * FROM #temp

Monday, March 28, 2022 - 4:35:13 PM - Alex Back To Top (89941)
I got it why it fails. That database has a space in its name and the script you created somehow stops when it gets to any a database with the space in the name. In TEST I turned off the one and stopped failed to the next one, tried many but we have lots. I wonder how that we can added as a fix...many thanks agian !!!

Monday, March 28, 2022 - 4:16:15 PM - Alex Back To Top (89940)
It seems to work but with issues. When I run first time the create table I get this:
Msg 911, Level 16, State 1, Line 1
Database 'PerformancePoint' does not exist. Make sure that the name is entered correctly.

I updated the procedure which is a system one. I used the script you added to create the temp table and when I select everything from it I see only all the users of some databases not all from the isntance. Maybe because is a temp table is limited to 1020 rows?
I tried to create a regular table to overcome this issue but when I select is empty.

Please help one more time, we are so close to get it resolved 100% . Many thanks.

Friday, March 25, 2022 - 5:49:22 PM - Greg Robidoux Back To Top (89933)
Hi Alex,

I made a change to the stored procedure to include Database Name.

There is also another new section of code I added to get this information all in one table.

-Greg

Friday, March 25, 2022 - 4:35:21 PM - Alex Back To Top (89932)
I looked already, cannot make it work to run on all dbs and pull together same result for all dbs-

Friday, March 25, 2022 - 12:22:05 PM - Greg Robidoux Back To Top (89930)
Hi Alex,

take a look at this article: https://www.mssqltips.com/sqlservertip/1414/run-same-command-on-all-sql-server-databases-without-cursors/

-Greg

Friday, March 25, 2022 - 11:57:26 AM - Alex Back To Top (89928)
Works, the only issue left is how to get the results for all databases of the instance.

Wednesday, March 23, 2022 - 10:34:50 AM - Greg Robidoux Back To Top (89921)
Hi Alex,

Just try this query and see if it works. I think the issue is that when the function is created it is only reading data from that database. I will see if we can update this article.

Try this code.

SELECT
User_Type =
CASE mmbrp.[type]
WHEN 'G' THEN 'Windows Group'
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
END,
Database_User_Name = mmbrp.[name],
Login_Name = ul.[name],
DB_Role = rolp.[name]
FROM sys.database_role_members mmbr, -- The Role OR members associations table
sys.database_principals rolp, -- The DB Roles names table
sys.database_principals mmbrp, -- The Role members table (database users)
sys.server_principals ul -- The Login accounts table
WHERE Upper (mmbrp.[type]) IN ( 'S', 'U', 'G' )
-- No need for these system account types
AND Upper (mmbrp.[name]) NOT IN ('SYS','INFORMATION_SCHEMA')
AND rolp.[principal_id] = mmbr.[role_principal_id]
AND mmbrp.[principal_id] = mmbr.[member_principal_id]
AND ul.[sid] = mmbrp.[sid]
AND rolp.[name] LIKE '%'

Wednesday, March 23, 2022 - 10:20:45 AM - Greg Robidoux Back To Top (89920)
Hi Alex,

Sorry I wish I had an answer for you.

I tried this on SQL Server 2012 and it works fine. I don't have SQL 2014 installed, so I can't test that version.

-Greg

Tuesday, March 22, 2022 - 8:19:37 PM - Alex Back To Top (89918)
I tried on various instances. I am DBA so I uses sysadmin. SQL 2014 version ... does not work as simple as that.

Tuesday, March 22, 2022 - 10:20:34 AM - Greg Robidoux Back To Top (89913)
Hi Alex,

Do you have another SQL Server instance you could try this on to see if it works?

Also, what server and database level permissions do you have? Just curious based on the error message you got. Also, when you try this for another database does it give you the same "dummy_DbUserRoles" as part of the error?

-Greg

Monday, March 21, 2022 - 9:35:12 PM - Alex Back To Top (89909)
2014

Thursday, March 17, 2022 - 9:34:51 AM - Greg Robidoux Back To Top (89896)
Hi Alex,

what version of SQL Server are you using?

-Greg

Wednesday, March 16, 2022 - 9:07:44 PM - Alex Back To Top (89890)
Not sure what you mean, but I tried everything, and the script gets me the error I mentioned below. Does not work for any database.

Tuesday, March 15, 2022 - 6:40:32 PM - Greg Robidoux Back To Top (89887)
Hi Alex,

Does this work if you try for a different database?

-Greg

Tuesday, March 15, 2022 - 4:27:21 PM - Alex Back To Top (89886)
The script at the bottom does not work , error:
Msg 4701, Level 16, State 1, Line 6
Cannot find the object "dummy_DbUserRoles" because it does not exist or you do not have permissions.

Monday, January 24, 2022 - 1:13:39 AM - Pavan Back To Top (89686)
How can I get the the all database user roles for SQL Server instance.

The below code it was not working. there might be different view and table we need to create before use the below code which was not present.

Friday, July 17, 2020 - 3:52:25 AM - Arvi Laanemets Back To Top (86149)

I finaly got all database user roles for SQL Server instance - thanks to your code! The working code follows:

DECLARE @DBID int, @DBNAME VARCHAR(50), @INSTANCENAME VARCHAR(50)
DECLARE @GetDbUserOU VARCHAR(MAX), @SQL_SCRIPT VARCHAR(MAX)

SET @INSTANCENAME = CAST(ServerProperty('servername')AS VARCHAR(30))

TRUNCATE TABLE MyAdminDB.dbo.dummy_DbUserRoles

SELECT @DBID=MIN(database_id) FROM MyAdminDB.dbo.view_instance_databases

WHILE @DBID Is Not Null
BEGIN

SELECT @DBNAME=database_name FROM MyAdminDB.dbo.view_instance_databases WHERE database_id = @DBID

SET @GetDbUserOU = 
'
USE {DBNAME}

DECLARE @dbRole varchar(255)
SET @dbRole = ''%''

INSERT INTO MyAdminDB.dbo.dummy_DbUserRoles (InstanceName, DbName, LoginName, DbRole, DbUserName, DbUserType)
SELECT 
''' + @INSTANCENAME +''' AS InstanceName,
''' + @DBNAME +''' AS DbName,
Login_Name = ul.[name],
DB_Role = rolp.[name],
Database_User_Name = mmbrp.[name],
User_Type = 
CASE mmbrp.[type] 
WHEN ''G'' THEN ''Windows Group'' 
WHEN ''S'' THEN ''SQL User'' 
WHEN ''U'' THEN ''Windows User'' 
END
FROM 
sys.database_role_members mmbr, -- The Role OR members associations table
sys.database_principals rolp,     -- The DB Roles names table
sys.database_principals mmbrp,    -- The Role members table (database users)
sys.server_principals ul          -- The Login accounts table
WHERE 
Upper (mmbrp.[type]) IN ( ''S'', ''U'', ''G'' )
-- No need for these system account types
AND Upper (mmbrp.[name]) NOT IN (''SYS'',''INFORMATION_SCHEMA'')
AND rolp.[principal_id] = mmbr.[role_principal_id]
AND mmbrp.[principal_id] = mmbr.[member_principal_id]
AND ul.[sid] = mmbrp.[sid]
AND rolp.[name] LIKE ''%'' + @dbRole + ''%''
'
SET @SQL_SCRIPT = REPLACE(@GetDbUserOU, '{DBNAME}', @DBNAME)
EXECUTE (@SQL_SCRIPT)

SELECT @DBID=MIN(database_id) FROM MyAdminDB.dbo.view_instance_databases WHERE database_id > @DBID

END

TRUNCATE TABLE MyAdminDB.dbo.DbUserRoles

INSERT INTO MyAdminDB.dbo.DbUserRoles SELECT * FROM MyAdminDB.dbo.dummy_DbUserRoles

TRUNCATE TABLE MyAdminDB.dbo.dummy_DbUserRoles

Wednesday, July 8, 2020 - 4:55:05 AM - Arvi Laanemets Back To Top (86101)

How can I run this function with database name as parameter? E.g. I created the function in database 'utilities', and I call it from any databas on this SQL Server instance and ask database roles mapping for any database on this instance. The end goal is either to run a procedure which requeries roles mappings for all databases on instance, or when it is possible, then to create a recursive query, which does the same on one go.


Monday, December 23, 2019 - 10:36:30 AM - Daniel Back To Top (83503)

Thanks alot! one question, i can see i need to run this script on each database and list the users for that database. How can i change it so it lists all the database and users in the entire instance? 


Thursday, April 11, 2019 - 5:32:41 AM - Olu Back To Top (79527)

Brilliant! Very simple and very helpful. It will really help us to keep tabs on who has access to what



download














get free sql tips
agree to terms