April 1, 2015 at 1:46 pm
I have been asked to provide a script that can be ran against all our SQL Server that will show permissions of Windows groups and Windows users. I have the code below which starts at the server level to show the Windows Groups and Principals and then gets down to the database logins to show the mapped logins. I am having trouble adding in the ability to show any role names associated with the database logins. The next thing after this is I want to show what permissions are assigned to the Roles. I know I am going to need sys.database_role_members to show the role names. Can someone help me out?
SELECT SP.name AS ServerPrincipalName
, SP.principal_id
, SP.[sid]
, DP.name AS DBPrincipalName
, DP.principal_id
, DP.owning_principal_id
, DP.[type]
, DP.type_desc AS DatabasePrincipalDescription
FROM sys.server_principals AS SP
LEFT JOIN sys.database_principals AS DP ON SP.sid = DP.sid
WHERE DP.[type] IN ('G', 'U') --Windows Group or Windows User
April 1, 2015 at 2:17 pm
The database system view <database name>.Sys.Database_Role_Members is what you want.
The view contains two columns:
role_principal_id
member_principal_id
The role_principal_id is the principal_id of the role itself, and the member_principal_id is the principal_id of the database user that is a member of the role.
You'll have to join the <database name>.sys.Database_Principals view twice, once for the user name and once for the role name.
Hope this helps.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply