Avem nevoie uneori de un raport care sa ne arate ce useri apartin unui rol in serverul de sql (in sql-ul rolurile sunt similare grupurilor in windows).

Pentru aceasta am creat o procedura care , rulata pe o baza de date, returneaza pentru acea baza rolurilegrupurile definite pe acea baza de date si userii aferenti fiecarui rolgrup de pe acea baza.

Procedura are un parametru @rol, care este folosit in sensul ca, daca primeste o valoare, procedura va returna toti userii aferenti rolului respectiv, iar daca parametrul @rol va avea valuare string vid ” (sau daca procedura se va rula fara parametru), atunci procedura va returna toate rolurile cu toti userii aferenti fiecarui rol de pe acea baza.

— exec BD_UseriPeRol_prc ”

create procedure BD_UseriPeRol_prc
@rol varchar(max) = ”

as

if @rol = ”
begin

WITH perms_cte as
(
select USER_NAME(p.grantee_principal_id) AS principal_name,
dp.principal_id,
p.state_desc AS permission_state_desc
from sys.database_permissions p
inner JOIN sys.database_principals dp
on p.grantee_principal_id = dp.principal_id
)

SELECT
distinct
rm.member_principal_name,
rm.principal_type_desc,
rm.role_name
FROM perms_cte p
right outer JOIN (
select role_principal_id, dp.type_desc as principal_type_desc,
member_principal_id,user_name(member_principal_id) as member_principal_name,
user_name(role_principal_id) as role_name–,*
from sys.database_role_members rm
INNER JOIN sys.database_principals dp
ON rm.member_principal_id = dp.principal_id
) rm
ON rm.role_principal_id = p.principal_id
order by 3, 1

end

ELSE

begin

WITH perms_cte as
(
select USER_NAME(p.grantee_principal_id) AS principal_name,
dp.principal_id,
p.state_desc AS permission_state_desc
from sys.database_permissions p
inner JOIN sys.database_principals dp
on p.grantee_principal_id = dp.principal_id
)

SELECT
distinct
rm.member_principal_name,
rm.principal_type_desc,
rm.role_name
FROM perms_cte p
right outer JOIN (
select role_principal_id, dp.type_desc as principal_type_desc,
member_principal_id,user_name(member_principal_id) as member_principal_name,
user_name(role_principal_id) as role_name–,*
from sys.database_role_members rm
INNER JOIN sys.database_principals dp
ON rm.member_principal_id = dp.principal_id
) rm
ON rm.role_principal_id = p.principal_id
where role_name = @rol
order by 1

end