SQL Server query to find all permissions/access for all users in a database

I would like to write a query on a sql 2008 that will report all the users that have access to a specific database, or objects within the database such as tables, views, and stored procedures, either directly or due to roles, etc. This report would be used for security auditing purposes. Not sure if anyone has a query that will fit my needs completely, but hopefully something that will give me a good start. Either sql 2008, 2005 or 2000 will do, I can probably convert as needed.

18 Answers

This is my first crack at a query, based on Andomar's suggestions. This query is intended to provide a list of permissions that a user has either applied directly to the user account, or through roles that the user has.

/* Security Audit Report 1) List all access provisioned to a sql user or windows user/group directly 2) List all access provisioned to a sql user or windows user/group through a database or application role 3) List all access provisioned to the public role Columns Returned: UserName : SQL or Windows/Active Directory user account. This could also be an Active Directory group. UserType : Value will be either 'SQL User' or 'Windows User'. This reflects the type of user defined for the SQL Server user account. DatabaseUserName: Name of the associated user as defined in the database user account. The database user may not be the same as the server user. Role : The role name. This will be null if the associated permissions to the object are defined at directly on the user account, otherwise this will be the name of the role that the user is a member of. PermissionType : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ObjectType : Type of object the user/role is assigned permissions on. Examples could include USER_TABLE, SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ObjectName : Name of the object that the user/role is assigned permissions on. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ColumnName : Name of the column of the object that the user/role is assigned permissions on. This value is only populated if the object is a table, view or a table value function. */ --List all access provisioned to a sql user or windows user/group directly SELECT [UserName] = CASE princ.[type] WHEN 'S' THEN princ.[name] WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI END, [UserType] = CASE princ.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' THEN 'Windows User' END, [DatabaseUserName] = princ.[name], [Role] = null, [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = obj.type_desc,--perm.[class_desc], [ObjectName] = OBJECT_NAME(perm.major_id), [ColumnName] = col.[name] FROM --database user sys.database_principals princ LEFT JOIN --Login accounts sys.login_token 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] WHERE princ.[type] in ('S','U') UNION --List all access provisioned to a sql user or windows user/group through a database or application role SELECT [UserName] = CASE memberprinc.[type] WHEN 'S' THEN memberprinc.[name] WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI END, [UserType] = CASE memberprinc.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' THEN 'Windows User' END, [DatabaseUserName] = memberprinc.[name], [Role] = roleprinc.[name], [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = obj.type_desc,--perm.[class_desc], [ObjectName] = OBJECT_NAME(perm.major_id), [ColumnName] = col.[name] FROM --Role/member associations sys.database_role_members members JOIN --Roles sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id] JOIN --Role members (database users) sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id] LEFT JOIN --Login accounts sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid] LEFT JOIN --Permissions sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[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] UNION --List all access provisioned to the public role, which everyone gets by default SELECT [UserName] = '{All Users}', [UserType] = '{All Users}', [DatabaseUserName] = '{All Users}', [Role] = roleprinc.[name], [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = obj.type_desc,--perm.[class_desc], [ObjectName] = OBJECT_NAME(perm.major_id), [ColumnName] = col.[name] FROM --Roles sys.database_principals roleprinc LEFT JOIN --Role permissions sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] LEFT JOIN --Table columns sys.columns col on col.[object_id] = perm.major_id AND col.[column_id] = perm.[minor_id] JOIN --All objects sys.objects obj ON obj.[object_id] = perm.[major_id] WHERE --Only roles roleprinc.[type] = 'R' AND --Only public role roleprinc.[name] = 'public' AND --Only objects of ours, not the MS objects obj.is_ms_shipped = 0 ORDER BY princ.[Name], OBJECT_NAME(perm.major_id), col.[name], perm.[permission_name], perm.[state_desc], obj.type_desc--perm.[class_desc]

Here is a complete version of Jeremy's Aug 2011 query with the changes suggested by Brad (Oct 2011) and iw.kuchin (May 2012) incorporated:

Brad: Correct [ObjectType] and [ObjectName] for schemas.
iw.kuchin: For [ObjectType] it's better to use obj.type_desc only for OBJECT_OR_COLUMN permission class. For all other cases use perm.[class_desc].
iw.kuchin: Handle IMPERSONATE permissions.
iw.kuchin: Replace sys.login_token with sys.server_principals as it will show also SQL Logins, not only Windows ones.
iw.kuchin: Include Windows groups.
iw.kuchin: Exclude users sys and INFORMATION_SCHEMA.

Hopefully this saves someone else an hour or two of their lives. :)

/* Security Audit Report 1) List all access provisioned to a SQL user or Windows user/group directly 2) List all access provisioned to a SQL user or Windows user/group through a database or application role 3) List all access provisioned to the public role Columns Returned: UserType : Value will be either 'SQL User', 'Windows User', or 'Windows Group'. This reflects the type of user/group defined for the SQL Server account. DatabaseUserName: Name of the associated user as defined in the database user account. The database user may not be the same as the server user. LoginName : SQL or Windows/Active Directory user account. This could also be an Active Directory group. Role : The role name. This will be null if the associated permissions to the object are defined at directly on the user account, otherwise this will be the name of the role that the user is a member of. PermissionType : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ObjectType : Type of object the user/role is assigned permissions on. Examples could include USER_TABLE, SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. Schema : Name of the schema the object is in. ObjectName : Name of the object that the user/role is assigned permissions on. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ColumnName : Name of the column of the object that the user/role is assigned permissions on. This value is only populated if the object is a table, view or a table value function. */ --1) List all access provisioned to a SQL user or Windows user/group directly SELECT [UserType] = CASE princ.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' THEN 'Windows User' WHEN 'G' THEN 'Windows Group' END, [DatabaseUserName] = princ.[name], [LoginName] = ulogin.[name], [Role] = NULL, [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = CASE perm.[class] WHEN 1 THEN obj.[type_desc] -- Schema-contained objects ELSE perm.[class_desc] -- Higher-level objects END, [Schema] = objschem.[name], [ObjectName] = CASE perm.[class] WHEN 3 THEN permschem.[name] -- Schemas WHEN 4 THEN imp.[name] -- Impersonations ELSE OBJECT_NAME(perm.[major_id]) -- General objects END, [ColumnName] = col.[name] FROM --Database user sys.database_principals AS princ --Login accounts LEFT JOIN sys.server_principals AS ulogin ON ulogin.[sid] = princ.[sid] --Permissions LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = princ.[principal_id] LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id] LEFT JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id] LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id] --Table columns LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id] AND col.[column_id] = perm.[minor_id] --Impersonations LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id] WHERE princ.[type] IN ('S','U','G') -- No need for these system accounts AND princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA') UNION --2) List all access provisioned to a SQL user or Windows user/group through a database or application role SELECT [UserType] = CASE membprinc.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' THEN 'Windows User' WHEN 'G' THEN 'Windows Group' END, [DatabaseUserName] = membprinc.[name], [LoginName] = ulogin.[name], [Role] = roleprinc.[name], [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = CASE perm.[class] WHEN 1 THEN obj.[type_desc] -- Schema-contained objects ELSE perm.[class_desc] -- Higher-level objects END, [Schema] = objschem.[name], [ObjectName] = CASE perm.[class] WHEN 3 THEN permschem.[name] -- Schemas WHEN 4 THEN imp.[name] -- Impersonations ELSE OBJECT_NAME(perm.[major_id]) -- General objects END, [ColumnName] = col.[name] FROM --Role/member associations sys.database_role_members AS members --Roles JOIN sys.database_principals AS roleprinc ON roleprinc.[principal_id] = members.[role_principal_id] --Role members (database users) JOIN sys.database_principals AS membprinc ON membprinc.[principal_id] = members.[member_principal_id] --Login accounts LEFT JOIN sys.server_principals AS ulogin ON ulogin.[sid] = membprinc.[sid] --Permissions LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id] LEFT JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id] LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id] --Table columns LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id] AND col.[column_id] = perm.[minor_id] --Impersonations LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id] WHERE membprinc.[type] IN ('S','U','G') -- No need for these system accounts AND membprinc.[name] NOT IN ('sys', 'INFORMATION_SCHEMA') UNION --3) List all access provisioned to the public role, which everyone gets by default SELECT [UserType] = '{All Users}', [DatabaseUserName] = '{All Users}', [LoginName] = '{All Users}', [Role] = roleprinc.[name], [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = CASE perm.[class] WHEN 1 THEN obj.[type_desc] -- Schema-contained objects ELSE perm.[class_desc] -- Higher-level objects END, [Schema] = objschem.[name], [ObjectName] = CASE perm.[class] WHEN 3 THEN permschem.[name] -- Schemas WHEN 4 THEN imp.[name] -- Impersonations ELSE OBJECT_NAME(perm.[major_id]) -- General objects END, [ColumnName] = col.[name] FROM --Roles sys.database_principals AS roleprinc --Role permissions LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id] --All objects JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id] LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id] --Table columns LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id] AND col.[column_id] = perm.[minor_id] --Impersonations LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id] WHERE roleprinc.[type] = 'R' AND roleprinc.[name] = 'public' AND obj.[is_ms_shipped] = 0 ORDER BY [UserType], [DatabaseUserName], [LoginName], [Role], [Schema], [ObjectName], [ColumnName], [PermissionType], [PermissionState], [ObjectType]

From SQL Server 2005 on, you can use system views for that. For example, this query lists all users in a database, with their rights:

select princ.name , princ.type_desc , perm.permission_name , perm.state_desc , perm.class_desc , object_name(perm.major_id) from sys.database_principals princ left join sys.database_permissions perm on perm.grantee_principal_id = princ.principal_id

Be aware that a user can have rights through a role as well. For example, the db_data_reader role grants select rights on most objects.

Can't comment on accepted answer so I'll add some comments here:

I second Brad on schemas issue. From MS reference sys.objects table contains only schema-scoped objects. So to get info about "higher level" objects (i.e. schemas in our case) you need to use sys.schemas table.
For [ObjectType] it's better to use obj.type_desc only for OBJECT_OR_COLUMN permission class. For all other cases use perm.[class_desc]
Another type of permission which is not handled so well with this query is IMPERSONATE. To get info about impersonations one should LEFT JOIN with sys.database_principals on perm.major_id = imp.principal_id
With my experience it's better to replace sys.login_token with sys.server_principals as it will show also SQL Logins, not only Windows ones
One should add 'G' to allowed principal types to allow Windows groups
Also, one can exclude users sys and INFORMATION_SCHEMA from resulting table, as these users are used only for service

I'll post first piece of script with all proposed fixes, other parts should be changed as well:

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, [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [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')

The other answers that I have seen miss some permissions that are possible in the database. The first query in the code below will get the database level permission for everything that is not a system object. It generates the appropriate GRANT statements as well. The second query gets all the role meberships.

This has to be run for each database, but is too long to use with sp_MSforeachdb. If you want to do that you'd have to add it to the master database as a system stored procedure.

To cover all possibilities you'd also have to have a script that checks the server level permissions.

SELECT DB_NAME() AS database_name , class , class_desc , major_id , minor_id , grantee_principal_id , grantor_principal_id , databasepermissions.type , permission_name , STATE , state_desc , granteedatabaseprincipal.name AS grantee_name , granteedatabaseprincipal.type_desc AS grantee_type_desc , granteeserverprincipal.name AS grantee_principal_name , granteeserverprincipal.type_desc AS grantee_principal_type_desc , grantor.name AS grantor_name , granted_on_name , permissionstatement + N' TO ' + QUOTENAME(granteedatabaseprincipal.name) + CASE WHEN STATE = N'W' THEN N' WITH GRANT OPTION' ELSE N'' END AS permissionstatement FROM ( SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(CONVERT(NVARCHAR(MAX), DB_NAME())) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS permissionstatement FROM sys.database_permissions WHERE (sys.database_permissions.class = 0) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.schemas.name) + N'.' + QUOTENAME(sys.objects.name) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ' + QUOTENAME(sys.schemas.name) + N'.' + QUOTENAME(sys.objects.name) + COALESCE(N' (' + QUOTENAME(sys.columns.name) + N')', N'') AS permissionstatement FROM sys.database_permissions INNER JOIN sys.objects ON sys.objects.object_id = sys.database_permissions.major_id INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.objects.schema_id LEFT OUTER JOIN sys.columns ON sys.columns.object_id = sys.database_permissions.major_id AND sys.columns.column_id = sys.database_permissions.minor_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 1) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.schemas.name) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON SCHEMA::' + QUOTENAME(sys.schemas.name) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 3) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(targetPrincipal.name) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ' + targetPrincipal.type_desc + N'::' + QUOTENAME(targetPrincipal.name) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.database_principals AS targetPrincipal ON targetPrincipal.principal_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 4) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.assemblies.name) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ASSEMBLY::' + QUOTENAME(sys.assemblies.name) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.assemblies ON sys.assemblies.assembly_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 5) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.types.name) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON TYPE::' + QUOTENAME(sys.types.name) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.types ON sys.types.user_type_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 6) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.types.name) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON TYPE::' + QUOTENAME(sys.types.name) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.types ON sys.types.user_type_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 6) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.xml_schema_collections.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON XML SCHEMA COLLECTION::' + QUOTENAME(sys.xml_schema_collections.name) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.xml_schema_collections ON sys.xml_schema_collections.xml_collection_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 10) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.service_message_types.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON MESSAGE TYPE::' + QUOTENAME(sys.service_message_types.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.service_message_types ON sys.service_message_types.message_type_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 15) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.service_contracts.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON CONTRACT::' + QUOTENAME(sys.service_contracts.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.service_contracts ON sys.service_contracts.service_contract_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 16) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.services.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON SERVICE::' + QUOTENAME(sys.services.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.services ON sys.services.service_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 17) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.remote_service_bindings.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON REMOTE SERVICE BINDING::' + QUOTENAME(sys.remote_service_bindings.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.remote_service_bindings ON sys.remote_service_bindings.remote_service_binding_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 18) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.routes.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ROUTE::' + QUOTENAME(sys.routes.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.routes ON sys.routes.route_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 19) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.symmetric_keys.name) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ASYMMETRIC KEY::' + QUOTENAME(sys.symmetric_keys.name) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.symmetric_keys ON sys.symmetric_keys.symmetric_key_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 24) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.certificates.name) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON CERTIFICATE::' + QUOTENAME(sys.certificates.name) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.certificates ON sys.certificates.certificate_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 25) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.asymmetric_keys.name) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ASYMMETRIC KEY::' + QUOTENAME(sys.asymmetric_keys.name) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.asymmetric_keys ON sys.asymmetric_keys.asymmetric_key_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 26) ) AS databasepermissions INNER JOIN sys.database_principals AS granteedatabaseprincipal ON granteedatabaseprincipal.principal_id = grantee_principal_id LEFT OUTER JOIN sys.server_principals AS granteeserverprincipal ON granteeserverprincipal.sid = granteedatabaseprincipal.sid INNER JOIN sys.database_principals AS grantor ON grantor.principal_id = grantor_principal_id ORDER BY grantee_name, granted_on_name SELECT roles.name AS role_name , roles.principal_id , roles.type AS role_type , roles.type_desc AS role_type_desc , roles.is_fixed_role AS role_is_fixed_role , memberdatabaseprincipal.name AS member_name , memberdatabaseprincipal.principal_id AS member_principal_id , memberdatabaseprincipal.type AS member_type , memberdatabaseprincipal.type_desc AS member_type_desc , memberdatabaseprincipal.is_fixed_role AS member_is_fixed_role , memberserverprincipal.name AS member_principal_name , memberserverprincipal.type_desc member_principal_type_desc , N'ALTER ROLE ' + QUOTENAME(roles.name) + N' ADD MEMBER ' + QUOTENAME(memberdatabaseprincipal.name) AS AddRoleMembersStatement FROM sys.database_principals AS roles INNER JOIN sys.database_role_members ON sys.database_role_members.role_principal_id = roles.principal_id INNER JOIN sys.database_principals AS memberdatabaseprincipal ON memberdatabaseprincipal.principal_id = sys.database_role_members.member_principal_id LEFT OUTER JOIN sys.server_principals AS memberserverprincipal ON memberserverprincipal.sid = memberdatabaseprincipal.sid ORDER BY role_name , member_name

UPDATE: The following queries will retrieve server level permissions and memberships.

SELECT sys.server_permissions.class , sys.server_permissions.class_desc , sys.server_permissions.major_id , sys.server_permissions.minor_id , sys.server_permissions.grantee_principal_id , sys.server_permissions.grantor_principal_id , sys.server_permissions.type , sys.server_permissions.permission_name , sys.server_permissions.state , sys.server_permissions.state_desc , granteeserverprincipal.name AS grantee_principal_name , granteeserverprincipal.type_desc AS grantee_principal_type_desc , grantorserverprinicipal.name AS grantor_name , CASE WHEN sys.server_permissions.state = N'W' THEN N'GRANT' ELSE sys.server_permissions.state_desc END + N' ' + sys.server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' TO ' + QUOTENAME(granteeserverprincipal.name) AS permissionstatement FROM sys.server_principals AS granteeserverprincipal INNER JOIN sys.server_permissions ON sys.server_permissions.grantee_principal_id = granteeserverprincipal.principal_id INNER JOIN sys.server_principals AS grantorserverprinicipal ON grantorserverprinicipal.principal_id = sys.server_permissions.grantor_principal_id ORDER BY granteeserverprincipal.name , sys.server_permissions.permission_name SELECT roles.name AS server_role_name , roles.principal_id , roles.type AS role_type , roles.type_desc AS role_type_desc , roles.is_fixed_role AS role_is_fixed_role , memberserverprincipal.name AS member_principal_name , memberserverprincipal.principal_id AS member_principal_id , memberserverprincipal.type AS member_principal_type , memberserverprincipal.type_desc AS member_principal_type_desc , memberserverprincipal.is_fixed_role AS member_is_fixed_role , N'ALTER SERVER ROLE ' + QUOTENAME(roles.name) + N' ADD MEMBER ' + QUOTENAME(memberserverprincipal.name) AS AddRoleMembersStatement FROM sys.server_principals AS roles INNER JOIN sys.server_role_members ON sys.server_role_members.role_principal_id = roles.principal_id INNER JOIN sys.server_principals AS memberserverprincipal ON memberserverprincipal.principal_id = sys.server_role_members.member_principal_id WHERE roles.type = N'R' ORDER BY server_role_name , member_principal_name

Awesome script Jeremy and contributors! Thanks!

I have a s-ton of users, so running this for all users was a nightmare. I couldn't add comments, so I am posting the whole script with the changes. I added a variable + where clause so I can search for anything matching up to 5 characters in the user name (or all users when left blank). Nothing special, but I thought it would be helpful in some use cases.

DECLARE @p_userName NVARCHAR(5) = 'UName' -- Specify up to five characters here (or none for all users) /* Security Audit Report 1) List all access provisioned to a sql user or windows user/group directly 2) List all access provisioned to a sql user or windows user/group through a database or application role 3) List all access provisioned to the public role Columns Returned: UserName : SQL or Windows/Active Directory user cccount. This could also be an Active Directory group. UserType : Value will be either 'SQL User' or 'Windows User'. This reflects the type of user defined for the SQL Server user account. DatabaseUserName: Name of the associated user as defined in the database user account. The database user may not be the same as the server user. Role : The role name. This will be null if the associated permissions to the object are defined at directly on the user account, otherwise this will be the name of the role that the user is a member of. PermissionType : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT, DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ObjectType : Type of object the user/role is assigned permissions on. Examples could include USER_TABLE, SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ObjectName : Name of the object that the user/role is assigned permissions on. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ColumnName : Name of the column of the object that the user/role is assigned permissions on. This value is only populated if the object is a table, view or a table value function. */ DECLARE @userName NVARCHAR(4) = @p_UserName + '%' --List all access provisioned to a sql user or windows user/group directly SELECT [UserName] = CASE princ.[type] WHEN 'S' THEN princ.[name] WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI END, [UserType] = CASE princ.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' THEN 'Windows User' END, [DatabaseUserName] = princ.[name], [Role] = null, [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = obj.type_desc,--perm.[class_desc], [ObjectName] = OBJECT_NAME(perm.major_id), [ColumnName] = col.[name] FROM --database user sys.database_principals princ LEFT JOIN --Login accounts sys.login_token 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] WHERE princ.[type] in ('S','U') AND princ.[name] LIKE @userName -- Added this line --CSLAGLE UNION --List all access provisioned to a sql user or windows user/group through a database or application role SELECT [UserName] = CASE memberprinc.[type] WHEN 'S' THEN memberprinc.[name] WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI END, [UserType] = CASE memberprinc.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' THEN 'Windows User' END, [DatabaseUserName] = memberprinc.[name], [Role] = roleprinc.[name], [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = obj.type_desc,--perm.[class_desc], [ObjectName] = OBJECT_NAME(perm.major_id), [ColumnName] = col.[name] FROM --Role/member associations sys.database_role_members members JOIN --Roles sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id] JOIN --Role members (database users) sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id] LEFT JOIN --Login accounts sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid] LEFT JOIN --Permissions sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[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] WHERE memberprinc.[name] LIKE @userName -- Added this line --CSLAGLE UNION --List all access provisioned to the public role, which everyone gets by default SELECT [UserName] = '{All Users}', [UserType] = '{All Users}', [DatabaseUserName] = '{All Users}', [Role] = roleprinc.[name], [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = obj.type_desc,--perm.[class_desc], [ObjectName] = OBJECT_NAME(perm.major_id), [ColumnName] = col.[name] FROM --Roles sys.database_principals roleprinc LEFT JOIN --Role permissions sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] LEFT JOIN --Table columns sys.columns col on col.[object_id] = perm.major_id AND col.[column_id] = perm.[minor_id] JOIN --All objects sys.objects obj ON obj.[object_id] = perm.[major_id] WHERE --Only roles roleprinc.[type] = 'R' AND --Only public role roleprinc.[name] = 'public' AND --Only objects of ours, not the MS objects obj.is_ms_shipped = 0 ORDER BY princ.[Name], OBJECT_NAME(perm.major_id), col.[name], perm.[permission_name], perm.[state_desc], obj.type_desc--perm.[class_desc]

Here is my version, adapted from others. I spent 30 minutes just now trying to remember how I came up with this, and @Jeremy 's answer seems to be the core inspiration. I didn't want to update Jeremy's answer, just in case I introduced bugs, so I am posting my version of it here.

I suggest pairing the full script with some inspiration taken from Kenneth Fisher's T-SQL Tuesday: What Permissions Does a Specific User Have?: This will allow you to answer compliance/audit questions bottom-up, as opposed to top-down.

EXECUTE AS LOGIN = '<loginname>' SELECT token.name AS GroupNames FROM sys.login_token token JOIN sys.server_principals grp ON token.sid = grp.sid WHERE token.[type] = 'WINDOWS GROUP' AND grp.[type] = 'G' REVERT

To understand what this covers, consider Contoso\DB_AdventureWorks_Accounting Windows AD Group with member Contoso\John.Doe. John.Doe authenticates to AdventureWorks via server_principal Contoso\DB_AdventureWorks_Logins Windows AD Group. If someone asks you, "What permissions does John.Doe have?", you cannot answer that question with just the below script. You need to then iterate through each row returned by the below script and join it to the above script. (You may also need to normalize for stale name values via looking up the SID in your Active Directory provider.)

Here is the script, without incorporating such reverse look-up logic.

/* --Script source found at : Security Audit Report 1) List all access provisioned to a sql user or windows user/group directly 2) List all access provisioned to a sql user or windows user/group through a database or application role 3) List all access provisioned to the public role Columns Returned: UserName : SQL or Windows/Active Directory user account. This could also be an Active Directory group. UserType : Value will be either 'SQL User' or 'Windows User'. This reflects the type of user defined for the SQL Server user account. PrinciaplUserName: if UserName is not blank, then UserName else DatabaseUserName PrincipalType : Possible values are 'SQL User', 'Windows User', 'Database Role', 'Windows Group' DatabaseUserName : Name of the associated user as defined in the database user account. The database user may not be the same as the server user. Role : The role name. This will be null if the associated permissions to the object are defined at directly on the user account, otherwise this will be the name of the role that the user is a member of. PermissionType : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ObjectType : Type of object the user/role is assigned permissions on. Examples could include USER_TABLE, SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ObjectName : Name of the object that the user/role is assigned permissions on. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ColumnName : Name of the column of the object that the user/role is assigned permissions on. This value is only populated if the object is a table, view or a table value function. */ DECLARE @HideDatabaseDiagrams BIT = 1; --List all access provisioned to a sql user or windows user/group directly SELECT [UserName] = CASE dbprinc.[type] WHEN 'S' THEN dbprinc.[name] -- SQL User WHEN 'U' THEN sprinc.[name] -- Windows User WHEN 'R' THEN NULL -- Database Role WHEN 'G' THEN NULL -- Windows Group ELSE NULL END, [UserType] = CASE dbprinc.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' THEN 'Windows User' WHEN 'R' THEN NULL -- Database Role WHEN 'G' THEN NULL -- Windows Group ELSE dbprinc.[type] END, [PrincipalUserName] = COALESCE( CASE dbprinc.[type] WHEN 'S' THEN dbprinc.[name] -- SQL User WHEN 'U' THEN sprinc.[name] -- Windows User WHEN 'R' THEN NULL -- Database Role WHEN 'G' THEN NULL -- Windows Group ELSE NULL END, dbprinc.[name] ), [PrincipalType] = CASE dbprinc.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' THEN 'Windows User' WHEN 'R' THEN 'Database Role' WHEN 'G' THEN 'Windows Group' END, [DatabaseUserName] = dbprinc.[name], [Role] = null, [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = obj.[type_desc],--perm.[class_desc], [ObjectSchema] = OBJECT_SCHEMA_NAME(perm.major_id), [ObjectName] = OBJECT_NAME(perm.major_id), [ColumnName] = col.[name] FROM --database user sys.database_principals dbprinc LEFT JOIN --Login accounts sys.server_principals sprinc on dbprinc.[sid] = sprinc.[sid] LEFT JOIN --Permissions sys.database_permissions perm ON perm.[grantee_principal_id] = dbprinc.[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] WHERE dbprinc.[type] in ('S','U') AND CASE WHEN @HideDatabaseDiagrams = 1 AND dbprinc.[name] = 'guest' AND ( ( obj.type_desc = 'SQL_SCALAR_FUNCTION' AND OBJECT_NAME(perm.major_id) = 'fn_diagramobjects' ) OR ( obj.type_desc = 'SQL_STORED_PROCEDURE' AND OBJECT_NAME(perm.major_id) IN ( N'sp_alterdiagram', N'sp_creatediagram', N'sp_dropdiagram', N'sp_helpdiagramdefinition', N'sp_helpdiagrams', N'sp_renamediagram' ) ) ) THEN 0 ELSE 1 END = 1 UNION --List all access provisioned to a sql user or windows user/group through a database or application role SELECT [UserName] = CASE memberprinc.[type] WHEN 'S' THEN memberprinc.[name] WHEN 'U' THEN sprinc.[name] WHEN 'R' THEN NULL -- Database Role WHEN 'G' THEN NULL -- Windows Group ELSE NULL END, [UserType] = CASE memberprinc.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' THEN 'Windows User' WHEN 'R' THEN NULL -- Database Role WHEN 'G' THEN NULL -- Windows Group END, [PrincipalUserName] = COALESCE( CASE memberprinc.[type] WHEN 'S' THEN memberprinc.[name] WHEN 'U' THEN sprinc.[name] WHEN 'R' THEN NULL -- Database Role WHEN 'G' THEN NULL -- Windows Group ELSE NULL END, memberprinc.[name] ), [PrincipalType] = CASE memberprinc.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' THEN 'Windows User' WHEN 'R' THEN 'Database Role' WHEN 'G' THEN 'Windows Group' END, [DatabaseUserName] = memberprinc.[name], [Role] = roleprinc.[name], [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = obj.type_desc,--perm.[class_desc], [ObjectSchema] = OBJECT_SCHEMA_NAME(perm.major_id), [ObjectName] = OBJECT_NAME(perm.major_id), [ColumnName] = col.[name] FROM --Role/member associations sys.database_role_members members JOIN --Roles sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id] JOIN --Role members (database users) sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id] LEFT JOIN --Login accounts sys.server_principals sprinc on memberprinc.[sid] = sprinc.[sid] LEFT JOIN --Permissions sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[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] WHERE CASE WHEN @HideDatabaseDiagrams = 1 AND memberprinc.[name] = 'guest' AND ( ( obj.type_desc = 'SQL_SCALAR_FUNCTION' AND OBJECT_NAME(perm.major_id) = 'fn_diagramobjects' ) OR ( obj.type_desc = 'SQL_STORED_PROCEDURE' AND OBJECT_NAME(perm.major_id) IN ( N'sp_alterdiagram', N'sp_creatediagram', N'sp_dropdiagram', N'sp_helpdiagramdefinition', N'sp_helpdiagrams', N'sp_renamediagram' ) ) ) THEN 0 ELSE 1 END = 1 UNION --List all access provisioned to the public role, which everyone gets by default SELECT [UserName] = '{All Users}', [UserType] = '{All Users}', [PrincipalUserName] = '{All Users}', [PrincipalType] = '{All Users}', [DatabaseUserName] = '{All Users}', [Role] = roleprinc.[name], [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = obj.type_desc,--perm.[class_desc], [ObjectSchema] = OBJECT_SCHEMA_NAME(perm.major_id), [ObjectName] = OBJECT_NAME(perm.major_id), [ColumnName] = col.[name] FROM --Roles sys.database_principals roleprinc LEFT JOIN --Role permissions sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] LEFT JOIN --Table columns sys.columns col on col.[object_id] = perm.major_id AND col.[column_id] = perm.[minor_id] JOIN --All objects sys.objects obj ON obj.[object_id] = perm.[major_id] WHERE --Only roles roleprinc.[type] = 'R' AND --Only public role roleprinc.[name] = 'public' AND --Only objects of ours, not the MS objects obj.is_ms_shipped = 0 AND CASE WHEN @HideDatabaseDiagrams = 1 AND roleprinc.[name] = 'public' AND ( ( obj.type_desc = 'SQL_SCALAR_FUNCTION' AND OBJECT_NAME(perm.major_id) = 'fn_diagramobjects' ) OR ( obj.type_desc = 'SQL_STORED_PROCEDURE' AND OBJECT_NAME(perm.major_id) IN ( N'sp_alterdiagram', N'sp_creatediagram', N'sp_dropdiagram', N'sp_helpdiagramdefinition', N'sp_helpdiagrams', N'sp_renamediagram' ) ) ) THEN 0 ELSE 1 END = 1 ORDER BY dbprinc.[Name], OBJECT_NAME(perm.major_id), col.[name], perm.[permission_name], perm.[state_desc], obj.type_desc--perm.[class_desc]

The GetPermissions Stored Procedure above is good however it uses Sp_msforeachdb which means that it will break if your SQL Instance has any databases names that include spaces or dashes and other non-best-practices characters. I have created a version that avoids the use of Sp_msforeachdb and also includes two columns that indicate 1 - if the Login is a sysadmin login (IsSysAdminLogin) and 2 - if the login is an orphan user (IsEmptyRow).

USE [master] ; GO IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.uspGetPermissionsOfAllLogins_DBsOnColumns') AND [type] in (N'P',N'PC') ) BEGIN DROP PROCEDURE dbo.uspGetPermissionsOfAllLogins_DBsOnColumns ; END GO CREATE PROCEDURE dbo.uspGetPermissionsOfAllLogins_DBsOnColumns AS SET NOCOUNT ON ; BEGIN TRY IF EXISTS ( SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].dbo.[#permission]') ) DROP TABLE #permission ; IF EXISTS ( SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].dbo.[#userroles_kk]') ) DROP TABLE #userroles_kk ; IF EXISTS ( SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].dbo.[#rolemember_kk]') ) DROP TABLE #rolemember_kk ; IF EXISTS ( SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].dbo.[##db_name]') ) DROP TABLE ##db_name ; DECLARE @db_name VARCHAR(255) ,@sql_text VARCHAR(MAX) ; SET @sql_text = 'CREATE TABLE ##db_name ( LoginUserName VARCHAR(MAX) ,' ; DECLARE cursDBs CURSOR FOR SELECT [name] FROM sys.databases ORDER BY [name] ; OPEN cursDBs ; FETCH NEXT FROM cursDBs INTO @db_name WHILE @@FETCH_STATUS = 0 BEGIN SET @sql_text = @sql_text + QUOTENAME(@db_name) + ' VARCHAR(MAX) ,' FETCH NEXT FROM cursDBs INTO @db_name END CLOSE cursDBs ; SET @sql_text = @sql_text + 'IsSysAdminLogin CHAR(1) ,IsEmptyRow CHAR(1) )' --PRINT @sql_text EXEC (@sql_text) ; DEALLOCATE cursDBs ; DECLARE @RoleName VARCHAR(255) ,@UserName VARCHAR(255) ; CREATE TABLE #permission ( LoginUserName VARCHAR(255) ,databasename VARCHAR(255) ,[role] VARCHAR(255) ) ; DECLARE cursSysSrvPrinName CURSOR FOR SELECT [name] FROM sys.server_principals WHERE [type] IN ( 'S', 'U', 'G' ) AND principal_id > 4 AND [name] NOT LIKE '##%' ORDER BY [name] ; OPEN cursSysSrvPrinName ; FETCH NEXT FROM cursSysSrvPrinName INTO @UserName WHILE @@FETCH_STATUS = 0 BEGIN CREATE TABLE #userroles_kk ( databasename VARCHAR(255) ,[role] VARCHAR(255) ) ; CREATE TABLE #rolemember_kk ( dbrole VARCHAR(255) ,membername VARCHAR(255) ,membersid VARBINARY(2048) ) ; DECLARE cursDatabases CURSOR FAST_FORWARD LOCAL FOR SELECT [name] FROM sys.databases ORDER BY [name] ; OPEN cursDatabases ; DECLARE @DBN VARCHAR(255) ,@sqlText NVARCHAR(4000) ; FETCH NEXT FROM cursDatabases INTO @DBN WHILE @@FETCH_STATUS = 0 BEGIN SET @sqlText = N'USE ' + QUOTENAME(@DBN) + '; TRUNCATE TABLE #RoleMember_kk INSERT INTO #RoleMember_kk EXEC sp_helprolemember INSERT INTO #UserRoles_kk (DatabaseName,[Role]) SELECT db_name(),dbRole FROM #RoleMember_kk WHERE MemberName = ''' + @UserName + ''' ' --PRINT @sqlText ; EXEC sp_executesql @sqlText ; FETCH NEXT FROM cursDatabases INTO @DBN END CLOSE cursDatabases ; DEALLOCATE cursDatabases ; INSERT INTO #permission SELECT @UserName 'user' ,b.name ,u.[role] FROM sys.sysdatabases b LEFT JOIN #userroles_kk u ON QUOTENAME(u.databasename) = QUOTENAME(b.name) ORDER BY 1 ; DROP TABLE #userroles_kk ; DROP TABLE #rolemember_kk ; FETCH NEXT FROM cursSysSrvPrinName INTO @UserName END CLOSE cursSysSrvPrinName ; DEALLOCATE cursSysSrvPrinName ; TRUNCATE TABLE ##db_name ; DECLARE @d1 VARCHAR(MAX) ,@d2 VARCHAR(MAX) ,@d3 VARCHAR(MAX) ,@ss VARCHAR(MAX) ; DECLARE cursPermisTable CURSOR FOR SELECT * FROM #permission ORDER BY 2 DESC ; OPEN cursPermisTable ; FETCH NEXT FROM cursPermisTable INTO @d1,@d2,@d3 WHILE @@FETCH_STATUS = 0 BEGIN IF NOT EXISTS ( SELECT 1 FROM ##db_name WHERE LoginUserName = @d1 ) BEGIN SET @ss = 'INSERT INTO ##db_name(LoginUserName) VALUES (''' + @d1 + ''')' EXEC (@ss) ; SET @ss = 'UPDATE ##db_name SET ' + @d2 + ' = ''' + @d3 + ''' WHERE LoginUserName = ''' + @d1 + '''' EXEC (@ss) ; END ELSE BEGIN DECLARE @var NVARCHAR(MAX) ,@ParmDefinition NVARCHAR(MAX) ,@var1 NVARCHAR(MAX) ; SET @var = N'SELECT @var1 = ' + QUOTENAME(@d2) + ' FROM ##db_name WHERE LoginUserName = ''' + @d1 + '''' ; SET @ParmDefinition = N'@var1 NVARCHAR(600) OUTPUT ' ; EXECUTE Sp_executesql @var,@ParmDefinition,@var1 = @var1 OUTPUT ; SET @var1 = ISNULL(@var1, ' ') ; SET @var = ' UPDATE ##db_name SET ' + @d2 + '=''' + @var1 + ' ' + @d3 + ''' WHERE LoginUserName = ''' + @d1 + ''' ' ; EXEC (@var) ; END FETCH NEXT FROM cursPermisTable INTO @d1,@d2,@d3 END CLOSE cursPermisTable ; DEALLOCATE cursPermisTable ; UPDATE ##db_name SET IsSysAdminLogin = 'Y' FROM ##db_name TT INNER JOIN dbo.syslogins SL ON TT.LoginUserName = SL.[name] WHERE SL.sysadmin = 1 ; DECLARE cursDNamesAsColumns CURSOR FAST_FORWARD LOCAL FOR SELECT [name] FROM tempdb.sys.columns WHERE OBJECT_ID = OBJECT_ID('tempdb..##db_name') AND [name] NOT IN ('LoginUserName','IsEmptyRow') ORDER BY [name] ; OPEN cursDNamesAsColumns ; DECLARE @ColN VARCHAR(255) ,@tSQLText NVARCHAR(4000) ; FETCH NEXT FROM cursDNamesAsColumns INTO @ColN WHILE @@FETCH_STATUS = 0 BEGIN SET @tSQLText = N'UPDATE ##db_name SET IsEmptyRow = ''N'' WHERE IsEmptyRow IS NULL AND ' + QUOTENAME(@ColN) + ' IS NOT NULL ; ' --PRINT @tSQLText ; EXEC sp_executesql @tSQLText ; FETCH NEXT FROM cursDNamesAsColumns INTO @ColN END CLOSE cursDNamesAsColumns ; DEALLOCATE cursDNamesAsColumns ; UPDATE ##db_name SET IsEmptyRow = 'Y' WHERE IsEmptyRow IS NULL ; UPDATE ##db_name SET IsSysAdminLogin = 'N' FROM ##db_name TT INNER JOIN dbo.syslogins SL ON TT.LoginUserName = SL.[name] WHERE SL.sysadmin = 0 ; SELECT * FROM ##db_name ; DROP TABLE ##db_name ; DROP TABLE #permission ; END TRY BEGIN CATCH DECLARE @cursDBs_Status INT ,@cursSysSrvPrinName_Status INT ,@cursDatabases_Status INT ,@cursPermisTable_Status INT ,@cursDNamesAsColumns_Status INT ; SELECT @cursDBs_Status = CURSOR_STATUS('GLOBAL','cursDBs') ,@cursSysSrvPrinName_Status = CURSOR_STATUS('GLOBAL','cursSysSrvPrinName') ,@cursDatabases_Status = CURSOR_STATUS('GLOBAL','cursDatabases') ,@cursPermisTable_Status = CURSOR_STATUS('GLOBAL','cursPermisTable') ,@cursDNamesAsColumns_Status = CURSOR_STATUS('GLOBAL','cursPermisTable') ; IF @cursDBs_Status > -2 BEGIN CLOSE cursDBs ; DEALLOCATE cursDBs ; END IF @cursSysSrvPrinName_Status > -2 BEGIN CLOSE cursSysSrvPrinName ; DEALLOCATE cursSysSrvPrinName ; END IF @cursDatabases_Status > -2 BEGIN CLOSE cursDatabases ; DEALLOCATE cursDatabases ; END IF @cursPermisTable_Status > -2 BEGIN CLOSE cursPermisTable ; DEALLOCATE cursPermisTable ; END IF @cursDNamesAsColumns_Status > -2 BEGIN CLOSE cursDNamesAsColumns ; DEALLOCATE cursDNamesAsColumns ; END SELECT ErrorNum = ERROR_NUMBER(),ErrorMsg = ERROR_MESSAGE() ; END CATCH GO /* EXEC [master].dbo.uspGetPermissionsOfAllLogins_DBsOnColumns ; */

CREATE PROCEDURE Get_permission AS DECLARE @db_name VARCHAR(200), @sql_text VARCHAR(max) SET @sql_text='Create table ##db_name (user_name varchar(max),' DECLARE db_cursor CURSOR FOR SELECT name FROM sys.databases OPEN db_cursor FETCH next FROM db_cursor INTO @db_name WHILE @@FETCH_STATUS = 0 BEGIN SET @sql_text=@sql_text + @db_name + ' varchar(max),' FETCH next FROM db_cursor INTO @db_name END CLOSE db_cursor SET @sql_text=@sql_text + 'Server_perm varchar(max))' EXEC (@sql_text) DEALLOCATE db_cursor DECLARE @RoleName VARCHAR(50) DECLARE @UserName VARCHAR(50) DECLARE @CMD VARCHAR(1000) CREATE TABLE #permission ( user_name VARCHAR(50), databasename VARCHAR(50), role VARCHAR(50) ) DECLARE longspcur CURSOR FOR SELECT name FROM sys.server_principals WHERE type IN ( 'S', 'U', 'G' ) AND principal_id > 4 AND name NOT LIKE '##%' AND name <> 'NT AUTHORITY\SYSTEM' AND name <> 'ONDEMAND\Administrator' AND name NOT LIKE 'steel%' OPEN longspcur FETCH next FROM longspcur INTO @UserName WHILE @@FETCH_STATUS = 0 BEGIN CREATE TABLE #userroles_kk ( databasename VARCHAR(50), role VARCHAR(50) ) CREATE TABLE #rolemember_kk ( dbrole VARCHAR(100), membername VARCHAR(100), membersid VARBINARY(2048) ) SET @CMD = 'use ? truncate table #RoleMember_kk insert into #RoleMember_kk exec sp_helprolemember insert into #UserRoles_kk (DatabaseName, Role) select db_name(), dbRole from #RoleMember_kk where MemberName = ''' + @UserName + '''' EXEC Sp_msforeachdb @CMD INSERT INTO #permission SELECT @UserName 'user', b.name, u.role FROM sys.sysdatabases b LEFT OUTER JOIN #userroles_kk u ON u.databasename = b.name --and u.Role='db_owner' ORDER BY 1 DROP TABLE #userroles_kk; DROP TABLE #rolemember_kk; FETCH next FROM longspcur INTO @UserName END CLOSE longspcur DEALLOCATE longspcur TRUNCATE TABLE ##db_name DECLARE @d1 VARCHAR(max), @d2 VARCHAR(max), @d3 VARCHAR(max), @ss VARCHAR(max) DECLARE perm_cur CURSOR FOR SELECT * FROM #permission ORDER BY 2 DESC OPEN perm_cur FETCH next FROM perm_cur INTO @d1, @d2, @d3 WHILE @@FETCH_STATUS = 0 BEGIN IF NOT EXISTS(SELECT 1 FROM ##db_name WHERE user_name = @d1) BEGIN SET @ss='insert into ##db_name(user_name) values (''' + @d1 + ''')' EXEC (@ss) SET @ss='update ##db_name set ' + @d2 + '=''' + @d3 + ''' where user_name=''' + @d1 + '''' EXEC (@ss) END ELSE BEGIN DECLARE @var NVARCHAR(max), @ParmDefinition NVARCHAR(max), @var1 NVARCHAR(max) SET @var = N'select @var1=' + @d2 + ' from ##db_name where USER_NAME=''' + @d1 + ''''; SET @ParmDefinition = N'@var1 nvarchar(300) OUTPUT'; EXECUTE Sp_executesql @var, @ParmDefinition, @var1=@var1 output; SET @var1=Isnull(@var1, ' ') SET @var= ' update ##db_name set ' + @d2 + '=''' + @var1 + ' ' + @d3 + ''' where user_name=''' + @d1 + ''' ' EXEC (@var) END FETCH next FROM perm_cur INTO @d1, @d2, @d3 END CLOSE perm_cur DEALLOCATE perm_cur SELECT * FROM ##db_name DROP TABLE ##db_name DROP TABLE #permission

I just added the following to Jeremy's answer because I had a role assigned to the database db_datareader which wasn't showing the permissions that role had. I tried going through all of the answers in everyone's posts but couldn't find anything that would do this so I added my own query.

 SELECT UserType='Role', DatabaseUserName = '{Role Members}', LoginName = DP2.name, Role = DP1.name, 'SELECT' AS [PermissionType] , [PermissionState] = 'GRANT', [ObjectType] = 'Table', [Schema] = 'dbo', [ObjectName] = 'All Tables', [ColumnName] = NULL FROM sys.database_role_members AS DRM RIGHT OUTER JOIN sys.database_principals AS DP1 ON DRM.role_principal_id = DP1.principal_id LEFT OUTER JOIN sys.database_principals AS DP2 ON DRM.member_principal_id = DP2.principal_id WHERE DP1.type = 'R' AND DP2.name IS NOT NULL

Great thanks for awesome audit scripts.

I highly recommend for audit user use awesome Kenneth Fisher (b | t) stored procedures:

Due to low rep can't reply with this to the people asking to run this on multiple databases/SQL Servers.

Create a registered server group and query across them all us the following and just cursor through the databases:

--Make sure all ' are doubled within the SQL string. DECLARE @dbname VARCHAR(50) DECLARE @statement NVARCHAR(max) DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM MASTER.dbo.sysdatabases where name like '%DBName%' OPEN db_cursor FETCH NEXT FROM db_cursor INTO @dbname WHILE @@FETCH_STATUS = 0 BEGIN SELECT @statement = 'use '+@dbname +';'+ ' /* Security Audit Report 1) List all access provisioned to a SQL user or Windows user/group directly 2) List all access provisioned to a SQL user or Windows user/group through a database or application role 3) List all access provisioned to the public role Columns Returned: UserType : Value will be either ''SQL User'', ''Windows User'', or ''Windows Group''. This reflects the type of user/group defined for the SQL Server account. DatabaseUserName: Name of the associated user as defined in the database user account. The database user may not be the same as the server user. LoginName : SQL or Windows/Active Directory user account. This could also be an Active Directory group. Role : The role name. This will be null if the associated permissions to the object are defined at directly on the user account, otherwise this will be the name of the role that the user is a member of. PermissionType : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ObjectType : Type of object the user/role is assigned permissions on. Examples could include USER_TABLE, SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. Schema : Name of the schema the object is in. ObjectName : Name of the object that the user/role is assigned permissions on. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ColumnName : Name of the column of the object that the user/role is assigned permissions on. This value is only populated if the object is a table, view or a table value function. */ --1) List all access provisioned to a SQL user or Windows user/group directly SELECT [UserType] = CASE princ.[type] WHEN ''S'' THEN ''SQL User'' WHEN ''U'' THEN ''Windows User'' WHEN ''G'' THEN ''Windows Group'' END, [DatabaseUserName] = princ.[name], [LoginName] = ulogin.[name], [Role] = NULL, [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = CASE perm.[class] WHEN 1 THEN obj.[type_desc] -- Schema-contained objects ELSE perm.[class_desc] -- Higher-level objects END, [Schema] = objschem.[name], [ObjectName] = CASE perm.[class] WHEN 3 THEN permschem.[name] -- Schemas WHEN 4 THEN imp.[name] -- Impersonations ELSE OBJECT_NAME(perm.[major_id]) -- General objects END, [ColumnName] = col.[name] FROM --Database user sys.database_principals AS princ --Login accounts LEFT JOIN sys.server_principals AS ulogin ON ulogin.[sid] = princ.[sid] --Permissions LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = princ.[principal_id] LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id] LEFT JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id] LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id] --Table columns LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id] AND col.[column_id] = perm.[minor_id] --Impersonations LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id] WHERE princ.[type] IN (''S'',''U'',''G'') -- No need for these system accounts AND princ.[name] NOT IN (''sys'', ''INFORMATION_SCHEMA'') UNION --2) List all access provisioned to a SQL user or Windows user/group through a database or application role SELECT [UserType] = CASE membprinc.[type] WHEN ''S'' THEN ''SQL User'' WHEN ''U'' THEN ''Windows User'' WHEN ''G'' THEN ''Windows Group'' END, [DatabaseUserName] = membprinc.[name], [LoginName] = ulogin.[name], [Role] = roleprinc.[name], [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = CASE perm.[class] WHEN 1 THEN obj.[type_desc] -- Schema-contained objects ELSE perm.[class_desc] -- Higher-level objects END, [Schema] = objschem.[name], [ObjectName] = CASE perm.[class] WHEN 3 THEN permschem.[name] -- Schemas WHEN 4 THEN imp.[name] -- Impersonations ELSE OBJECT_NAME(perm.[major_id]) -- General objects END, [ColumnName] = col.[name] FROM --Role/member associations sys.database_role_members AS members --Roles JOIN sys.database_principals AS roleprinc ON roleprinc.[principal_id] = members.[role_principal_id] --Role members (database users) JOIN sys.database_principals AS membprinc ON membprinc.[principal_id] = members.[member_principal_id] --Login accounts LEFT JOIN sys.server_principals AS ulogin ON ulogin.[sid] = membprinc.[sid] --Permissions LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id] LEFT JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id] LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id] --Table columns LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id] AND col.[column_id] = perm.[minor_id] --Impersonations LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id] WHERE membprinc.[type] IN (''S'',''U'',''G'') -- No need for these system accounts AND membprinc.[name] NOT IN (''sys'', ''INFORMATION_SCHEMA'') UNION --3) List all access provisioned to the public role, which everyone gets by default SELECT [UserType] = ''{All Users}'', [DatabaseUserName] = ''{All Users}'', [LoginName] = ''{All Users}'', [Role] = roleprinc.[name], [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = CASE perm.[class] WHEN 1 THEN obj.[type_desc] -- Schema-contained objects ELSE perm.[class_desc] -- Higher-level objects END, [Schema] = objschem.[name], [ObjectName] = CASE perm.[class] WHEN 3 THEN permschem.[name] -- Schemas WHEN 4 THEN imp.[name] -- Impersonations ELSE OBJECT_NAME(perm.[major_id]) -- General objects END, [ColumnName] = col.[name] FROM --Roles sys.database_principals AS roleprinc --Role permissions LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id] --All objects JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id] LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id] --Table columns LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id] AND col.[column_id] = perm.[minor_id] --Impersonations LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id] WHERE roleprinc.[type] = ''R'' AND roleprinc.[name] = ''public'' AND obj.[is_ms_shipped] = 0 ORDER BY [UserType], [DatabaseUserName], [LoginName], [Role], [Schema], [ObjectName], [ColumnName], [PermissionType], [PermissionState], [ObjectType] ' exec sp_executesql @statement FETCH NEXT FROM db_cursor INTO @dbname END CLOSE db_cursor DEALLOCATE db_cursor

This thread massively helped me thanks everyone!

I tried just about all of these but I quickly noticed that some were missing, especially sysadmin users. Having a hole like that won't look good in our upcoming audit, so this is what I came up with

USE master GO SELECT DISTINCT p.name AS [loginname] , --p.type, p.type_desc , p.is_disabled, s.sysadmin, sp.permission_name FROM sys.server_principals p INNER JOIN sys.syslogins s ON p.sid = s.sid INNER JOIN sys.server_permissions sp ON p.principal_id = sp.grantee_principal_id WHERE p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP') -- Logins that are not process logins AND p.name NOT LIKE '##%' ORDER BY p.name GO

A simple query that shows only whether you are a SysAdmin or not :

IF IS_SRVROLEMEMBER ('sysadmin') = 1 print 'Current user''s login is a member of the sysadmin role' ELSE IF IS_SRVROLEMEMBER ('sysadmin') = 0 print 'Current user''s login is NOT a member of the sysadmin role' ELSE IF IS_SRVROLEMEMBER ('sysadmin') IS NULL print 'ERROR: The server role specified is not valid.';

Here's the most popular answer submitted by Jeremy, but modified to include the sysadmin and disabled flags mentioned by Greg Sipes as well as a log_date_time column.

Best of both worlds?

/* Source: Security Audit Report 1) List all access provisioned to a sql user or windows user/group directly 2) List all access provisioned to a sql user or windows user/group through a database or application role 3) List all access provisioned to the public role Columns Returned: UserName : SQL or Windows/Active Directory user account. This could also be an Active Directory group. UserType : Value will be either 'SQL User' or 'Windows User'. This reflects the type of user defined for the SQL Server user account. DatabaseUserName: Name of the associated user as defined in the database user account. The database user may not be the same as the server user. Role : The role name. This will be null if the associated permissions to the object are defined at directly on the user account, otherwise this will be the name of the role that the user is a member of. PermissionType : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ObjectType : Type of object the user/role is assigned permissions on. Examples could include USER_TABLE, SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ObjectName : Name of the object that the user/role is assigned permissions on. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ColumnName : Name of the column of the object that the user/role is assigned permissions on. This value is only populated if the object is a table, view or a table value function. */ --List all access provisioned to a sql user or windows user/group directly SELECT [UserName] = CASE princ.[type] WHEN 'S' THEN princ.[name] WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI END, [UserType] = CASE princ.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' THEN 'Windows User' END, [DatabaseUserName] = princ.[name], [Role] = null, [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = obj.type_desc,--perm.[class_desc], [ObjectName] = OBJECT_NAME(perm.major_id), [ColumnName] = col.[name], sp.is_disabled, s.sysadmin, GETDATE() AS [log_date_time] FROM --database user sys.database_principals princ LEFT JOIN --Login accounts sys.login_token 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.syslogins s ON princ.sid = s.sid LEFT JOIN sys.server_principals sp ON princ.name = sp.name WHERE princ.[type] in ('S','U') UNION --List all access provisioned to a sql user or windows user/group through a database or application role SELECT [UserName] = CASE memberprinc.[type] WHEN 'S' THEN memberprinc.[name] WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI END, [UserType] = CASE memberprinc.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' THEN 'Windows User' END, [DatabaseUserName] = memberprinc.[name], [Role] = roleprinc.[name], [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = obj.type_desc,--perm.[class_desc], [ObjectName] = OBJECT_NAME(perm.major_id), [ColumnName] = col.[name], sp.is_disabled, s.sysadmin, GETDATE() AS [log_date_time] FROM --Role/member associations sys.database_role_members members JOIN --Roles sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id] JOIN --Role members (database users) sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id] LEFT JOIN --Login accounts sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid] LEFT JOIN --Permissions sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[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.syslogins s ON memberprinc.[sid] = s.sid LEFT JOIN sys.server_principals sp ON memberprinc.[name] = sp.name UNION --List all access provisioned to the public role, which everyone gets by default SELECT [UserName] = '{All Users}', [UserType] = '{All Users}', [DatabaseUserName] = '{All Users}', [Role] = roleprinc.[name], [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = obj.type_desc,--perm.[class_desc], [ObjectName] = OBJECT_NAME(perm.major_id), [ColumnName] = col.[name], sp.is_disabled, s.sysadmin, GETDATE() AS [log_date_time] FROM --Roles sys.database_principals roleprinc LEFT JOIN --Role permissions sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[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.syslogins s ON roleprinc.sid = s.sid LEFT JOIN sys.server_principals sp ON roleprinc.name = sp.name JOIN --All objects sys.objects obj ON obj.[object_id] = perm.[major_id] WHERE --Only roles roleprinc.[type] = 'R' AND --Only public role roleprinc.[name] = 'public' AND --Only objects of ours, not the MS objects obj.is_ms_shipped = 0 ORDER BY princ.[Name], OBJECT_NAME(perm.major_id), col.[name], perm.[permission_name], perm.[state_desc], obj.type_desc--perm.[class_desc]

Unfortunately I couldn't comment on the Sean Rose post due to insufficient reputation, however I had to amend the "public" role portion of the script as it didn't show SCHEMA-scoped permissions due to the (INNER) JOIN against sys.objects. After that was changed to a LEFT JOIN I further had to amend the WHERE-clause logic to omit system objects. My amended query for the public perms is below.

--3) List all access provisioned to the public role, which everyone gets by default SELECT @@servername ServerName , db_name() DatabaseName , [UserType] = '{All Users}', [DatabaseUserName] = '{All Users}', [LoginName] = '{All Users}', [Role] = roleprinc.[name], [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = CASE perm.[class] WHEN 1 THEN obj.[type_desc] -- Schema-contained objects ELSE perm.[class_desc] -- Higher-level objects END, [Schema] = objschem.[name], [ObjectName] = CASE perm.[class] WHEN 3 THEN permschem.[name] -- Schemas WHEN 4 THEN imp.[name] -- Impersonations ELSE OBJECT_NAME(perm.[major_id]) -- General objects END, [ColumnName] = col.[name] FROM --Roles sys.database_principals AS roleprinc --Role permissions LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id] --All objects LEFT JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id] LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id] --Table columns LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id] AND col.[column_id] = perm.[minor_id] --Impersonations LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id] WHERE roleprinc.[type] = 'R' AND roleprinc.[name] = 'public' AND isnull(obj.[is_ms_shipped], 0) = 0 AND isnull(object_schema_name(perm.[major_id]), '') <> 'sys' ORDER BY [UserType], [DatabaseUserName], [LoginName], [Role], [Schema], [ObjectName], [ColumnName], [PermissionType], [PermissionState], [ObjectType]

If you want check access to databases for a particular login has use this simple script as below:

sys.sp_helplogins @LoginNamePattern = 'Domain\login' -- sysname

--ok my turn to contribute back,enjoy

This report header dynamically grabs the SQL Instance name, date\time, and account name the report is run by, all things a good auditor will want to know. :)

Note - if you have a extended property called 'environment' on the Master database, the value (whatever you use: PreProd, Development, Production, DR, etc) will be included in the report header.

BEGIN BEGIN TRY SET NOCOUNT ON SELECT 'See Messages Tab..... use Ctrl+SHIFT+F and re-run to ''send to file''' DECLARE @DBName nvarchar(2000) = DB_NAME() DECLARE @User_Name nvarchar(200) = suser_sname() DECLARE @Account_Name nvarchar(200) DECLARE @Granted_permissions nvarchar(2000) DECLARE @Permission_State nvarchar(200) DECLARE @ParentObject nvarchar(200) DECLARE @env2 varchar(50) = Convert(varchar(50),(Select ServerProperty('Servername'))); DECLARE @day varchar(50) = FORMAT (getdate(), 'dddd, MM, yyyy'); DECLARE @clk varchar(50) = FORMAT (getdate(), 'hh:mm:ss tt') ; DECLARE @env1 VARCHAR(25) = (SELECT CAST(value AS varchar(25)) FROM [master].[sys].fn_listextendedproperty('environment', default, default, default, default, default, default)); PRINT '*** ' + @DBName + ' Security Audit Report ***'; PRINT ' in the ' + @env1 + ' environment'; PRINT ' on SQL Instance: ' + @env2; PRINT ' '+ @day + ' at ' + @clk; PRINT ' run under account ' + @User_Name; PRINT ' ' CREATE TABLE #GP( DBName NVARCHAR(200), Account_Name NVARCHAR(200), Granted_Permissions NVARCHAR(max), Permission_State NVARCHAR(200), ParentObject NVARCHAR(200) ) ;WITH SampleDataR AS (SELECT DB_NAME() AS 'DBName' ,dp.name AS 'Account_Name' ,dpm.permission_name AS 'Granted_Permissions' ,dpm.state_desc AS 'Permission_State' ,dpm.class_desc AS 'ParentObject' , ROW_NUMBER() OVER (PARTITION BY DB_NAME(), dp.[name] ,dpm.state_desc, dpm.class_desc ORDER BY permission_name) rownum FROM sys.database_principals dp LEFT OUTER JOIN [sys].[database_permissions] dpm ON dp.principal_id = dpm.grantee_principal_id WHERE dp.type ='R' AND dp.sid IS NOT NULL AND dp.name <> 'public' AND dp.name NOT LIKE 'db_a%' AND dp.name NOT LIKE 'db_b%' AND dp.name NOT LIKE 'db_d%' AND dp.name NOT LIKE 'db_o%' AND dp.name NOT LIKE 'db_s%' --AND dpm.class_desc = 'DATABASE' -- remove to see schema based permissions ) --Select * from SampleDataR INSERT INTO #GP SELECT DISTINCT DBName ,Account_Name ,(SELECT Granted_Permissions + CASE WHEN s1.rownum = (select MAX(rownum) FROM SampleDataR WHERE DBName = s1.DBName AND Account_Name = s1.Account_Name AND ParentObject = s1.ParentObject) THEN ' (' + Permission_State + '), ' ELSE ' (' + Permission_State + '), ' END FROM SampleDataR s1 WHERE s1.DBName = s2.DBName AND s1.Account_Name = s2.Account_Name AND s1.ParentObject = s2.ParentObject FOR xml path(''),type).value('(.)[1]','varchar(max)' ) Granted_Permissions ,Permission_State ,ParentObject FROM SampleDataR s2 --Select * from #GP PRINT 'Assigned Role Permissions' PRINT ' ' SET NOCOUNT ON DECLARE cur CURSOR FOR SELECT DISTINCT DBName, Account_Name, ParentObject, Granted_permissions FROM #GP OPEN cur SET NOCOUNT ON FETCH NEXT FROM cur INTO @DBname, @Account_Name, @ParentObject, @Granted_permissions; WHILE @@FETCH_STATUS = 0 BEGIN PRINT @DBName + ', ' + @Account_Name + ', ' + '[' + @ParentObject + '], ' + @Granted_permissions FETCH NEXT FROM cur INTO @DBname, @Account_Name, @ParentObject , @Granted_permissions; END CLOSE cur; DEALLOCATE cur; SET NOCOUNT ON DROP Table #GP SET NOCOUNT ON DECLARE @DBName2 nvarchar(200) DECLARE @Account_Name2 nvarchar(200) DECLARE @Granted_permissions2 nvarchar(200) CREATE TABLE #GP2( DBName NVARCHAR(200), Account_Name NVARCHAR(200) , Granted_Permissions NVARCHAR(200) ) ;WITH SampleDataR AS (SELECT DB_NAME() AS 'DBName' ,dp.name AS 'Account_Name' --,dp.type ,dpm.permission_name ,ROW_NUMBER() OVER (PARTITION BY DB_NAME(), dp.[name] ORDER BY permission_name) rownum FROM sys.database_principals dp LEFT OUTER JOIN [sys].[database_permissions] dpm ON dp.principal_id = dpm.grantee_principal_id --order by dp.type WHERE dp.type not in ('A', 'R', 'X') --removed 'G', AND dp.sid is not null AND dp.name not in ('guest','dbo') ) INSERT INTO #GP2 SELECT DISTINCT DBName ,Account_Name ,(SELECT permission_name + CASE WHEN s1.rownum = (select MAX(rownum) FROM SampleDataR WHERE DBName = s1.DBName and Account_Name = s1.Account_Name ) THEN '' ELSE ',' END FROM SampleDataR s1 WHERE s1.DBName = s2.DBName AND s1.Account_Name = s2.Account_Name FOR xml path(''),type).value('(.)[1]','varchar(max)') Granted_Permissions FROM SampleDataR s2; PRINT ' ' PRINT ' ' PRINT 'Assigned User Permissions' PRINT ' ' DECLARE cur CURSOR FOR SELECT DBName, Account_Name, Granted_permissions FROM #GP2 OPEN cur SET NOCOUNT ON FETCH NEXT FROM cur INTO @DBname2, @Account_Name2, @Granted_permissions2; WHILE @@FETCH_STATUS = 0 BEGIN PRINT @DBName2 + ', ' + @Account_Name2 + ', ' + @Granted_permissions2 FETCH NEXT FROM cur INTO @DBname2, @Account_Name2, @Granted_permissions2; END CLOSE cur; DEALLOCATE cur; DROP TABLE #GP2 SET NOCOUNT ON DECLARE @DBName3 nvarchar(200) DECLARE @Role_Name3 nvarchar(max) DECLARE @Members3 nvarchar(max) CREATE TABLE #GP3( DBName NVARCHAR(200), Role_Name NVARCHAR(max), members NVARCHAR(max) ) ;WITH SampleDataR AS (SELECT DB_NAME() AS 'DBName' ,r.name AS 'role_name' ,m.name AS 'members' ,ROW_NUMBER() OVER (PARTITION BY DB_NAME(), r.[name] ORDER BY m.[name]) rownum FROM sys.database_role_members rm INNER JOIN sys.database_principals r on rm.role_principal_id = r.principal_id INNER JOIN sys.database_principals m on rm.member_principal_id = m.principal_id ) INSERT INTO #GP3 SELECT DISTINCT DBName ,Role_Name ,(SELECT Members + CASE WHEN s3.rownum = (select MAX(rownum) FROM SampleDataR WHERE DBName = s3.DBName and Role_Name = s3.Role_Name ) THEN ',' ELSE ',' END FROM SampleDataR s1 WHERE s1.DBName = s3.DBName and s1.Role_Name = s3.Role_Name FOR xml path(''),type).value('(.)[1]','varchar(max)') Members FROM SampleDataR s3 PRINT ' ' PRINT ' ' PRINT 'Assigned Role Membership' PRINT ' ' DECLARE cur CURSOR FOR SELECT DBName, Role_Name, Members FROM #GP3 OPEN cur SET NOCOUNT ON FETCH NEXT FROM cur INTO @DBname3, @Role_Name3, @Members3; WHILE @@FETCH_STATUS = 0 BEGIN PRINT @DBName3 + ', ' + @Role_Name3 + ', ' + @Members3 FETCH NEXT FROM cur INTO @DBname3, @Role_Name3, @Members3; END CLOSE cur; DEALLOCATE cur; DROP Table #GP3; END TRY BEGIN CATCH SELECT 'Real ERROR at Line #' + CAST(ERROR_LINE() AS VARCHAR(20)); -- Throw/raise and error caught from the Try section. THROW; END CATCH;

END

--great to save as a stored proc

Gossip Burst

SQL Server query to find all permissions/access for all users in a database

18 Answers

Your Answer

Sign up or log in

Post as a guest

You Might Also Like

error LNK2005: _main already defined in hold.obj

.length cannot be resolved or is not a field

@Transactional(propagation=Propagation.REQUIRED)

Best way to format if statement with multiple conditions