In cazul in care avem nevoie sa stim, pentru un anumit user, ce tipuri de drepturi are in baza de date si pe ce obiecte,     am facut  o procedura care, rulata pe o baza, intoarce userii, obiectele pe care userii au drepturi, tipul de obiect (tabela, procedura, view, etc)  tipul de drept pe obiectul respectiv (select, update, delete, etc)  si starea tipului de drept (adica daca are GRANT sau DENY pe acel tip de drept pe obiectul respectiv).

Procedura are un parametru @user care, daca primeste o valoare, procedura va returna pentru acel user toate drepturile pe toate obiectele pe care acel user are drept.

Daca parametrul @user va fi lasat strig vid ” (sau daca procedura va fi rulata fara parametru), atunci procedura va returna toti userii cu toate drepturile de pe toate obiectele aferente bazei pe care se ruleaza.

— exec BD_DrepturiPeUseriSiObiecte_prc ”

create procedure BD_DrepturiPeUseriSiObiecte_prc
@user varchar(max) = ”
as

SELECT
[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],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,
[ObjectName] = OBJECT_NAME(perm.major_id)
into #tabela_intermediara
FROM
sys.database_principals princ
LEFT JOIN
sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN
sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
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

SELECT
[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],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,
[ObjectName] = OBJECT_NAME(perm.major_id)
FROM
sys.database_role_members members
JOIN
sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN
sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
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

SELECT
[UserName] = ‘{All Users}’,
[UserType] = ‘{All Users}’,
[DatabaseUserName] = ‘{All Users}’,
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,
[ObjectName] = OBJECT_NAME(perm.major_id)
FROM
sys.database_principals roleprinc
LEFT JOIN
sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
sys.columns col on col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
JOIN
sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
roleprinc.[type] = ‘R’ AND
roleprinc.[name] = ‘public’ AND
obj.is_ms_shipped = 0
ORDER BY
princ.[Name],
OBJECT_NAME(perm.major_id),
perm.[permission_name],
perm.[state_desc],
obj.type_desc

if @user = ”
begin
select *
from #tabela_intermediara
order by 1
end
else
begin
select * from #tabela_intermediara where [UserName] = @user
end