SQL Server Database Users to Roles Mapping Report

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).

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:

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):

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):

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.

Eli Leiba

Eli Leiba is a senior application DBA at Israel Electric Company, a teacher at Microsoft CTEC, and a senior database consultant. With 19 years of experience working with both SQL Server and Oracle RDBMS. He is certified in Oracle and SQL Server database administration and implementation and has a B.S. in Computer Science. He can be reached at: iecdba@hotmail.com.

• MSSQLTips Awards: Rising Star (50+ tips) – 2019 | Author of the Year Contender – 2016-2019