Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Database Users to Roles Mapping Report


By:   |   Last Updated: 2019-04-11   |   Comments (1)   |   Related Tips: More > 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 simple T-SQL in-line table function that generates a users to database roles mapping. This function should be created and used in each application database.

The function 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 function (dbo.dbRolesUsersMap) joins to (according to the system relationship model) three system tables:

sys.database_role_members s 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 function 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 function 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 Function to Show Users to Database Roles Mapping

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- ================================================ 
-- Author:      Eli Leiba         
-- Create date: 24-03-2019            
-- Description: Simple Users to Database Roles mappings function           
-- ================================================
CREATE FUNCTION dbo.dbRolesUsersMap (@dbRole SYSNAME = '%')
RETURNS TABLE
AS
RETURN (
      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 '%' + @dbRole + '%'
      )
GO

Sample Execution with the SQL Server In-Line Table Function

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

USE NorthwindTrainingDB
GO

SELECT * FROM dbo.dbRolesUsersMap (DEFAULT)
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

SELECT * FROM dbo.dbRolesUsersMap ('db_ddlAdmin')
GO			

Here are the results (on my server):

Results for DB-DDLAdmin
Next Steps
  • You can create and compile this simple function in each of your application databases and use it as a simple tool to generate a fast database user to database roles reports.
  • Note that the function 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 function was tested with for SQL Server 2014 and 2017, but it 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 all started with the SQL Server 2008 version.


Last Updated: 2019-04-11


next webcast button


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
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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


Learn more about SQL Server tools