Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

T-SQL to list all users and their permission for all databases of a server? Expand / Collapse
Author
Message
Posted Friday, March 19, 2010 8:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 29, 2011 7:49 AM
Points: 107, Visits: 626
I need T-SQL script to list all users and their permission for all databases of a server?
Post #886424
Posted Friday, December 07, 2012 10:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 11:14 AM
Points: 4, Visits: 56
--Audit User Permissions

SELECT [UserName] = ulogin.[name],
[UserType] = CASE princ.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'G' THEN 'Windows Group'
END,
[DatabaseUserName] = princ.[name],
[Role] = NULL,
[PermissionState] = perm.[state_desc],
[PermissionType] = perm.[permission_name],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.type_desc -- Schema-contained objects
ELSE perm.[class_desc] -- Higher-level objects
END,
[ObjectName] = CASE perm.[class]
WHEN 1 THEN OBJECT_NAME(perm.major_id) -- General objects
WHEN 3 THEN schem.[name] -- Schemas
WHEN 4 THEN imp.[name] -- Impersonations
END,
[ColumnName] = col.[name]
FROM --database user
sys.database_principals princ
LEFT JOIN --Login accounts
sys.server_principals ulogin
ON princ.[sid] = ulogin.[sid]
LEFT JOIN --Permissions
sys.database_permissions perm
ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN --Table columns
sys.columns col
ON col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
LEFT JOIN sys.objects obj
ON perm.[major_id] = obj.[object_id]
LEFT JOIN sys.schemas schem
ON schem.[schema_id] = perm.[major_id]
LEFT JOIN sys.database_principals imp
ON imp.[principal_id] = perm.[major_id]
WHERE princ.[type] IN ('S', 'U', 'G')
AND -- No need for these system accounts
princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')
ORDER BY
ulogin.[name],
[UserType],
[DatabaseUserName],
[Role],
[PermissionState],
[PermissionType],
[ObjectType],
[ObjectName],
[ColumnName]



Post #1394156
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse