How to find all database users and their role membership?

You need to generate security reports to retrieve the database users and their role memberships for SOX or other auditing reports requirements or for security troubleshooting purposes. SQL Server has a number of system tables when joined together can help you produce the security report needed. These tables are:

  • sys.sysusers
  • sys.sysmembers
  • master.dbo.syslogins

While these system table are considered deprecated, they are still being supported on SQL Server 2012 for backward compatibility. I will be working on an updated version when time permits. In case you have your version of the script using the updated versions of the system tables and views, please share. Updated version is now available using the sys.database_role_members and sys.database_principals system tables at the bottom of this blog.

Note that I have specifically not using the undocumented and unsupported stored procedure SP_MSFOREACHDB as it misses databases. A simple WHILE loop can accomplish the same.
Here we go with the script:

-- find all database users and their role memberships
DECLARE @dbname nvarchar(123)
, @id int
, @max int
, @cmdDBUsersRoles nvarchar(max)

-- temp table to stored the databases
IF OBJECT_ID('tempdb..#db_list') IS NOT NULL
    DROP TABLE #db_list

CREATE TABLE #db_list
(
    id int identity (1,1)
    , dbname nvarchar(123)
);

-- temp table to store the databases, users, roles that users belong to
IF OBJECT_ID('tempdb..#dbs_users_roles') IS NOT NULL
	DROP TABLE #dbs_users_roles

CREATE TABLE #dbs_users_roles
(
	dbname nvarchar(123),
	dbuser nvarchar(123),
	dbrole nvarchar(123),
	account_type nvarchar(123),
	login_name nvarchar(123),
	default_db nvarchar(123)
);

-- load the database list into the temp table
INSERT INTO #db_list
SELECT db.name
FROM sys.databases db
WHERE db.state = 0;

-- initialize the counters 
SELECT @id = 1, @max = max(id)
FROM #db_list

-- loop to process 
WHILE (@id <= @max)
BEGIN
    SELECT @dbname = dbname
    FROM #db_list
    WHERE id = @id;

	SET @cmdDBUsersRoles = 'USE ' +@dbname+
		' SELECT DB_NAME() AS [Database Name],
				su.name AS [Database User],
				CASE 
					WHEN (ug.uid is null) THEN ''public''
					ELSE ug.name
				END AS [Database Role],
				CASE 
					WHEN su.isntuser=1 then ''Windows Domain Account''
					WHEN su.isntgroup = 1 then ''Windows Group''
					WHEN su.issqluser = 1 then ''SQL Account''
					WHEN su.issqlrole = 1 then ''SQL Role''
				END AS [Account Type],
				l.loginname AS [Login],
				l.dbname AS [Default DB]
			FROM
			   sys.sysusers su LEFT OUTER JOIN
			   (sys.sysmembers sm INNER JOIN sys.sysusers ug ON sm.groupuid = ug.uid) ON su.uid = sm.memberuid
				LEFT OUTER JOIN master.dbo.syslogins l on su.sid = l.sid
			WHERE
			   (su.islogin = 1 and su.hasdbaccess = 1) and
			   (ug.issqlrole = 1 or ug.uid is null)';

	INSERT INTO #dbs_users_roles
	EXEC (@cmdDBUsersRoles);

	SET @id = @id + 1;
END


SELECT dsr.dbname AS [Database Name],
	dsr.dbuser AS [Database User],
	dsr.dbrole AS [Database Role],
	dsr.account_type AS [Account Type],
	dsr.login_name AS [Login Name],
	dsr.default_db AS [Deafult Database]
FROM #dbs_users_roles AS dsr;

Updated on October 05, 2014 to use the sys.database_role_members and sys.database_principals system tables:

-- find all database users and their role memberships
DECLARE @dbname nvarchar(123)
, @id int
, @max int
, @cmdDBUsersRoles nvarchar(max)

-- temp table to stored the databases
IF OBJECT_ID('tempdb..#db_list') IS NOT NULL
    DROP TABLE #db_list

CREATE TABLE #db_list
(
    id int identity (1,1)
    , dbname nvarchar(123)
);

-- temp table to store the databases, users, roles that users belong to
IF OBJECT_ID('tempdb..#dbs_users_roles') IS NOT NULL
	DROP TABLE #dbs_users_roles

CREATE TABLE #dbs_users_roles
(
	dbname nvarchar(123),
	dbuser nvarchar(123),
	dbrole nvarchar(123),
	account_type nvarchar(123),
	create_date datetime,
	modify_date datetime
);

-- load the database list into the temp table
INSERT INTO #db_list
SELECT db.name
FROM sys.databases db
WHERE db.state = 0;

-- initialize the counters 
SELECT @id = 1, @max = max(id)
FROM #db_list

-- loop to process 
WHILE (@id <= @max)
BEGIN
    SELECT @dbname = dbname
    FROM #db_list
    WHERE id = @id;

	SET @cmdDBUsersRoles = 'USE ' +@dbname+
		' SELECT DB_NAME(), 
			dp.name,
			rp.name,
			dp.type_desc,
			dp.create_date,
			dp.modify_date
		FROM sys.database_role_members drm JOIN sys.database_principals rp
				ON drm.role_principal_id = rp.principal_id
			JOIN sys.database_principals dp
				ON drm.member_principal_id = dp.principal_id';

	INSERT INTO #dbs_users_roles
	EXEC (@cmdDBUsersRoles);

	SET @id = @id + 1;
END


SELECT dsr.dbname AS [Database Name],
	dsr.dbuser AS [Database User],
	dsr.dbrole AS [Database Role],
	dsr.account_type AS [Account Type],
	dsr.create_date,
	dsr.modify_date
FROM #dbs_users_roles AS dsr;
Be Sociable, Share!
You can leave a response, or trackback from your own site.

One Response to “How to find all database users and their role membership?”

Leave a Reply

You must be logged in to post a comment.