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

totally beautiful script. well written. thanks for this.

cheers

Great script you are the best

A real time saver thanks !!

Fantastic script. Saved me hours of work.

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’

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

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.

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

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

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

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

This is great! Added to my bookmarks!

This is a great piece of code … but need little more tweak add a piece of code — can some one please help me embedding the below code ??? — Really appreciate help..

SELECT prins.name
,is_disabled
,CAST(LoginProperty(prins.name, ‘IsExpired’) AS INT) is_expired
,CAST(LoginProperty(prins.name, ‘IsLocked’) AS INT) is_locked
FROM sys.server_principals prins
WHERE (is_disabled = 0 AND CAST(LoginProperty(prins.name, ‘IsExpired’) AS INT) = 1)
OR CAST(LoginProperty(prins.name, ‘IsLocked’) AS INT) = 1
ORDER BY name

I ran it on a SQL Server 2008 version without problem, and it really was a time saver (thanks for that), But when I tried on a SQL Server 2000 version I got these error messages

“Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near ‘max’.
Msg 197, Level 15, State 1, Line 29
EXECUTE cannot be used as a source when inserting into a table variable.
Msg 170, Level 15, State 1, Line 45
Line 45: Incorrect syntax near ‘XML’.”

It is a very useful script, do you have a SQL server 2000 version for it?

Very handy for auditing – many thanks for sharing

Be careful using this script – it doesn’t take into account any hierarchical roles, i.e. when roles are members of other roles.

Thank you for the script it worked great. I made only one change and that was to look by pararmeter for a user that I wanted to delete from several databases before I deleted the users from master.

Thank you again.

Thank you for an excellent query. It is what I was looking for fetching all user right on all databases.