SQL Server – Listar Permissões

Olá!

Um dos objetivos iniciais desse blog também é postar scripts ou situações que eu passo no dia-a-dia e que depois posso precisar novamente. Então hoje, só vou postar esse script que eu achei pela internet. Utilizei hoje ele para gerar uma lista de permissões concedidas em um determinado banco de dados para os usuários e roles, algo que constantemente eu preciso gerar devido a auditorias ou análises de segurança.

[sourcecode language=”sql”]
–List all access provisioned to a sql user or windows user/group directly
SELECT  DB_NAME(DB_ID()) as DBNAME,
[UserName] = CASE princ.[type]
WHEN ‘S’ THEN princ.[name]
WHEN ‘U’ THEN ulogin.[name] COLLATE Latin1_General_CI_AI
END,
[UserType] = CASE princ.[type]
WHEN ‘S’ THEN ‘SQL User’
WHEN ‘U’ THEN ‘Windows User’
END,
[DatabaseUserName] = princ.[name],
[Role] = null,
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,–perm.[class_desc],
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name]
FROM
–database user
sys.database_principals princ
LEFT JOIN
–Login accounts
sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN
–Permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
–Table columns
sys.columns col ON col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
LEFT JOIN
sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE
princ.[type] in (‘S’,’U’)
UNION
–List all access provisioned to a sql user or windows user/group through a database or application role
SELECT  DB_NAME(DB_ID()) as DBNAME,
[UserName] = CASE memberprinc.[type]
WHEN ‘S’ THEN memberprinc.[name]
WHEN ‘U’ THEN ulogin.[name] COLLATE Latin1_General_CI_AI
END,
[UserType] = CASE memberprinc.[type]
WHEN ‘S’ THEN ‘SQL User’
WHEN ‘U’ THEN ‘Windows User’
END,
[DatabaseUserName] = memberprinc.[name],
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,–perm.[class_desc],
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name]
FROM
–Role/member associations
sys.database_role_members members
JOIN
–Roles
sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
–Role members (database users)
sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
–Login accounts
sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN
–Permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
–Table columns
sys.columns col on col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
LEFT JOIN
sys.objects obj ON perm.[major_id] = obj.[object_id]
UNION
–List all access provisioned to the public role, which everyone gets by default
SELECT  DB_NAME(DB_ID()) as DBNAME,
[UserName] = ‘{All Users}’,
[UserType] = ‘{All Users}’,
[DatabaseUserName] = ‘{All Users}’,
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,–perm.[class_desc],
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name]
FROM
–Roles
sys.database_principals roleprinc
LEFT JOIN
–Role permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
–Table columns
sys.columns col on col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
JOIN
–All objects
sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
–Only roles
roleprinc.[type] = ‘R’ AND
–Only public role
roleprinc.[name] = ‘public’ AND
–Only objects of ours, not the MS objects
obj.is_ms_shipped = 0
ORDER BY
princ.[Name],
OBJECT_NAME(perm.major_id),
col.[name],
perm.[permission_name],
perm.[state_desc],
obj.type_desc–perm.[class_desc]
<pre>[/sourcecode]

Dúvidas é só comentar abaixo, valeu!

Vithor da Silva e Silva | DBA SQL Server
vithor@vssti.com.br


Fonte

Stack Overflow

Deixe uma resposta