SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


Get List of Database User Role Memberships for All Databases in SQL Server

SQL Server database administrators frequently require a list of SQL logins or Windows users granted access on a SQL Server instance with the databases and database roles they're mapped for that database. If a SQL Server instance is recently added to your responsibility, you might want to learn who can login to a database on that specific SQL Server instance. I hope this SQL Server tutorial will help you to visualize the database accesses on your SQL Server database instance.

In this tutorial, I want to provide DBA's and SQL developers a list of SQL queries and SQL scripts that will help them to get a list of database user role memberships for all databases on a target SQL Server instance.

STEP 1)

First of all SQL administrators can create a table in master database to store database users and the roles that the user is mapped to for each database on the related SQL Server instance.

CREATE TABLE EY_DatabaseRoles (
 dbname sysname,
 principle sysname,
 roles varchar(max)
)
Code

STEP 2)

The EY_DatabaseRoles table created in Master database will store data collected by the following SQL script which will be executed on each database on the target SQL Server instance.

select
 db_name() dbname,
 dp.name principle,
 rp.name role
from [sys].[database_role_members] drm
inner join [sys].[database_principals] rp on rp.principal_id = drm.role_principal_id
inner join [sys].[database_principals] dp on dp.principal_id = drm.member_principal_id
Code

The output is as follows.

SQL Server database role mappings to principals


STEP 3)

To run a script on each database created on a SQL Server database instance, there are two methods that comes to mind at once.
The first method that I used in this SQL tutorial is using sp_MSForEachDB SQL Server undocumented system procedure.
The second way to run a SQL script on all databases is using a SQL cursor and running the same query using EXEC statement.

EXEC sp_MSForEachDB '
 Use [?];
 INSERT INTO master..EY_DatabaseRoles
  select
   db_name() dbname,
   dp.name principle,
   rp.name role
  from [sys].[database_role_members] drm
  inner join [sys].[database_principals] rp on rp.principal_id = drm.role_principal_id
  inner join [sys].[database_principals] dp on dp.principal_id = drm.member_principal_id;'
Code

This query runs on each database and collects security information from sys.database_role_members and sys.database_principals system views.


STEP 4)

After data is collected in master database table EY_DatabaseRoles, we are ready to display and list SQL Server login users and mapped database roles using following query.

SELECT distinct
 e.dbname,
 e.principle,
 STUFF(
 (
  SELECT
   ',' + ISNULL(c.roles,'')
  FROM EY_DatabaseRoles c
  WHERE c.dbname = e.dbname and c.principle = e.principle
  FOR XML PATH('')
  ,TYPE
  ).value('.','VARCHAR(MAX)'
 ), 1, 1, ''
 ) As database_roles
FROM EY_DatabaseRoles e
Code

The final output granting the list of SQL Server principles or database users that is mapped to a database role on each database

SQL Server database roles granted to logins

I've worked successfully with the above SQL queries and scripts on SQL Server 2005 and SQL Server 2014 versions without a problem. If you need more detail about the SQL Server security, login users, server and database permissions you can benefit from the SQL Server System Views map posters available at Microsoft Downloads web portal.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.