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.