T-SQL: Retrieve all users and associated roles for ALL databases

Posted in: Technical Track

A frequent inquiry concerning databases’ security is to retrieve the database role(s) associated with each user for auditing or troubleshooting purposes.

Each database user (principal) can be retrieved from sys.database_principals and the associated database roles can be retrieved from sys.database_role_members

The following code runs against ALL the databases using SP_MSForeachdb and all roles for one principal is concatenated in one row


DECLARE @DB_USers TABLE
(DBName sysname, UserName sysname, LoginType sysname, AssociatedRole varchar(max),create_date datetime,modify_date datetime)

INSERT @DB_USers
EXEC sp_MSforeachdb

'
use [?]
SELECT ''?'' AS DB_Name,
case prin.name when ''dbo'' then prin.name + '' (''+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') + '')'' else prin.name end AS UserName,
prin.type_desc AS LoginType,
isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,create_date,modify_date
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%'''

SELECT

dbname,username ,logintype ,create_date ,modify_date ,

STUFF(

(

SELECT ',' + CONVERT(VARCHAR(500),associatedrole)

FROM @DB_USers user2

WHERE

user1.DBName=user2.DBName AND user1.UserName=user2.UserName

FOR XML PATH('')

)

,1,1,'') AS Permissions_user

FROM @DB_USers user1

GROUP BY

dbname,username ,logintype ,create_date ,modify_date

ORDER BY DBName,username

The code applies few filters:

– No Guest user account; you should NOT enable a guest account for a production database.

– Skip fixed database roles such as DB_OWNER , db_datawriter,db_datareader,….etc

– Principals with NULL SID ; those are internal to the DB such as INFORMATION_SCHEMA & sys

Remarks

– SYSADMINS don’t need to be part of explicit database’s users list to gain access as they have access to everything on server. You can get list of SYSADMIN users by running following code

SELECT
p.name AS [Name] ,r.type_desc,r.is_disabled,r.create_date , r.modify_date,r.default_database_name
FROM
sys.server_principals r
INNER JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id
INNER JOIN sys.server_principals p ON
p.principal_id = m.member_principal_id
WHERE r.type = 'R' and r.name = N'sysadmin'

– If a windows login is part of a windows group then you may need to look at the members of this Windows group to identify who can access this database.

– A user may still be revoked access to database indvidual objects even the role allows access normally.

Following blog has a query to list permissions on indvidual objects

http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions–_2800_2_2900_.aspx

HTH
M

Interested in working with Mohammed? Schedule a tech call.

17 Comments. Leave new

totally beautiful script. well written. thanks for this.

cheers

Reply

I keep receiving this msg when I execute the script.
Msg 515, Level 16, State 2, Line 4
Cannot insert the value NULL into column ‘UserName’, table ‘@DB_USers’; column does not allow nulls. INSERT fails.
The statement has been terminated.

(0 row(s) affected)

Reply

declare username sysname is null like below

DECLARE @DB_USers TABLE(DBName sysname, UserName sysname null,

Reply

I found this issue to be caused by invalid database owners, which I would want to correct when found. I changed the case statement in the MSforeachdb loop to look like the following:

case prin.name when ”dbo” then prin.name + ” (”+
coalesce((select SUSER_SNAME(owner_sid) from master.sys.databases where name =”?”),”Invalid Owner”) + ”)” else prin.name end AS UserName

When I encounter those rows in the results, I can change the database owner appropriately.

Reply

Great script you are the best

Reply

A real time saver thanks !!

Reply

Fantastic script. Saved me hours of work.

Reply

Hi – The last SQL block may not be quite right. The version of server_principals shown in the SELECT block should be the one aliased “p”, not “r”. This version may produce better results;

SELECT p.name AS [Name] ,
p.type_desc,
p.is_disabled ,
p.create_date ,
p.modify_date ,
p.default_database_name
FROM sys.server_principals r
INNER JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id
INNER JOIN sys.server_principals p ON p.principal_id = m.member_principal_id
AND p.is_disabled = 0
WHERE r.type = ‘R’
AND r.name = N’sysadmin’

Reply

Maybe this code would help someone:

BEGIN

DECLARE @Date SMALLDATETIME

SET @Date = CAST(CAST(GETDATE() AS DATE) AS SMALLDATETIME)

SELECT CONVERT(VARCHAR(250), SERVERPROPERTY(‘ServerName’)) AS ServerName, SP1.[name] AS [Login], SP2.[name] AS ServerRole, SP1.is_disabled AS IsDisabled, @Date AS ExtractDate
FROM sys.server_principals SP1
INNER JOIN sys.server_role_members SRM ON SP1.principal_id = SRM.member_principal_id
INNER JOIN sys.server_principals SP2 ON SRM.role_principal_id = SP2.principal_id
WHERE SP1.[name] NOT IN (‘NT Service\MSSQLSERVER’,’NT SERVICE\SQLSERVERAGENT’,’NT SERVICE\SQLWriter’,’NT SERVICE\Winmgmt’,
‘NT AUTHORITY\SYSTEM’,’NT AUTHORITY\NETWORK SERVICE’)
;

END

Reply
Abhinandan Aithal
December 17, 2013 8:11 am

Truly great script..Thank you:)

Reply
Sriharsha Gurram
February 21, 2014 6:32 am

Nice Script, it would be better if it has the ability to generate user creation and role allocation commands which can be used during database migrations.

Reply
Mohammed Mawla
June 4, 2014 5:13 pm

Hi Sriharsha ,
I’ll see if I can update it

Reply

Great script, thanks M! What would I need to change to have it include User Groups? (or run separately for User Groups)

Reply
Sajal Bagchi
June 28, 2014 2:36 pm

Pefect script. I wonder if we have the similar script to run against 2000 servers.

Reply

I get these errors
Msg 207, Level 16, State 1, Line 44
Invalid column name ‘dbname’.
Msg 207, Level 16, State 1, Line 44
Invalid column name ‘username’.
Msg 207, Level 16, State 1, Line 44
Invalid column name ‘logintype’.
Msg 207, Level 16, State 1, Line 20
Invalid column name ‘dbname’.
Msg 207, Level 16, State 1, Line 20
Invalid column name ‘username’.
Msg 207, Level 16, State 1, Line 20
Invalid column name ‘logintype’.
Msg 207, Level 16, State 1, Line 26
Invalid column name ‘associatedrole’.

When i delete create_date modify_date from selection i get

Msg 156, Level 15, State 1, Line 34
Incorrect syntax near the keyword ‘FOR’.

Any suggestions

Reply

I have the same error as Jameira. Is this script meant for sql server 2008?

Reply

I figured it out. The created table’s columns have the wrong case in many places. The below query fixes the case issue and runs fine.

DECLARE @DB_USers TABLE (dbname sysname, username sysname, logintype sysname, associatedrole varchar(max),create_date datetime,modify_date datetime)

INSERT @DB_USers
EXEC sp_MSforeachdb

use [?]
SELECT ”?” AS DB_Name,
case prin.name when ”dbo” then prin.name + ” (”+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =”?”) + ”)” else prin.name end AS UserName,
prin.type_desc AS LoginType,
isnull(USER_NAME(mem.role_principal_id),””) AS AssociatedRole ,create_date,modify_date
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
prin.is_fixed_role 1 AND prin.name NOT LIKE ”##%”’

SELECT dbname,username ,logintype ,create_date ,modify_date , STUFF((SELECT ‘,’ + CONVERT(VARCHAR(500),associatedrole)
FROM @DB_USers user2
WHERE user1.dbname=user2.dbname AND user1.username=user2.username
FOR XML PATH(”)) ,1,1,”) AS Permissions_user
FROM @DB_USers user1
GROUP BY dbname,username ,logintype ,create_date ,modify_date
ORDER BY dbname,username

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *