Business Excellence

Security Audit in SQL Server

Select schema-level permissions:

SELECT USER_NAME(p.grantee_principal_id) AS principal_name,
    dp.principal_id,
    dp.type_desc AS principal_type_desc,
    p.class_desc,
    p.major_id,
    p.permission_name,
    p.state_desc AS permission_state_desc 
    ,s.name AS schema_name
FROM sys.database_permissions p
INNER JOIN sys.database_principals dp
    ON p.grantee_principal_id = dp.principal_id
INNER JOIN sys.schemas s ON s.schema_id = p.major_id