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 Kodyaz SQL Server Tools Reviews and Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



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

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

The output is as follows.


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;'

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

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

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.






Related SQL Resources

SQL Server Articles

SQL Server 2016

SQL Server 2014

SQL Server 2012

SQL Server Tools

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems