Security Assignments in SQL Server

security

There is a need, as a Database Administrator in SQL Server, to review the security privileges for everyone, at the database level.  This query to be should be somewhat helpful in determining the roles that have been assigned for the currently selected database:

WITH RIGHTS AS
   (SELECT name AS RoleType, principal_id
    FROM sys.database_principals
    WHERE (type_desc = 'DATABASE_ROLE'))
SELECT PRINCIPALS.Name as PrincipalName, RIGHTS.RoleType 
FROM RIGHTS INNER JOIN sys.database_role_members AS MEMBERS
     ON RIGHTS.principal_id = MEMBERS.role_principal_id 
     INNER JOIN sys.database_principals AS PRINCIPALS
     ON
     PRINCIPALS.principal_id = MEMBERS.member_principal_id
ORDER BY PRINCIPALS.name, RIGHTS.RoleType; 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s