Userii ce sunt definiti pe un rol

Configurare noua (How To)

Situatie

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.

Solutie

— 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

Tip solutie

Permanent

Voteaza

(16 din 44 persoane apreciaza acest articol)

Despre Autor

Leave A Comment?