MS SQL Server – Determine principal’s schema level privileges

--place replace with actual database-name

use <db-name>;
go

DECLARE @user sysname

--please replace with actual user-name
set @user = 'user'

SELECT 

           grantee_principal.name 
         , grantee_principal.type 
         , s.name 
         , prmssn.permission_name

FROM  sys.schemas AS s

         INNER JOIN sys.database_permissions AS prmssn

            ON  prmssn.major_id=s.schema_id
            AND prmssn.minor_id=0
            AND prmssn.class=3

         INNER JOIN sys.database_principals AS grantee_principal

              ON grantee_principal.principal_id 

                     = prmssn.grantee_principal_id

WHERE grantee_principal.[name] = @user

Leave a Reply

Please log in using one of these methods to post your comment:

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