Este script mostra o nível de permissão por objeto (Compatível com SQL 2005 ou Superior:):
Exemplo 01:
============================
SELECT
USER_NAME(grantee_principal_id) AS 'User'
, state_desc AS 'Permission'
, permission_name AS 'Action'
, CASE class
WHEN 0 THEN 'Database::' + DB_NAME()
WHEN 1 THEN OBJECT_NAME(major_id)
WHEN 3 THEN 'Schema::' + SCHEMA_NAME(major_id) END AS 'Securable'
FROM sys.database_permissions dp
WHERE class IN (0, 1, 3)
AND minor_id = 0;
Mostra todos os logins com permissão de SA (Compatível: SQL 2005 ou >)
Exemplo 02:
============================
SELECT SP1.[name] AS 'Login', SP2.[name] AS 'ServerRole'
FROM sys.server_principals SP1
JOIN sys.server_role_members SRM
ON SP1.principal_id = SRM.member_principal_id
JOIN sys.server_principals SP2
ON SRM.role_principal_id = SP2.principal_id
ORDER BY SP1.[name], SP2.[name];