Vizualizare permisiuni user pe toate bazele de date de pe un server

Situatie

Am creat o procedura ce primeste ca parametru un nume de user si, pentru acel user, procedura returneaza permisiunile acelui user pe toate bazele de date de pe serverul sql pe care este rulata.

Procedura returneaza cate o linie pentru fiecare baza de date de pe server.

In cazul in care userul primit ca parametru nu are drept pe o baza, procedura returneaza o linie goala.

In cazul in care userul primit ca parametru are drept pe o baza, procedura returneaza:

Numele serverului

Numele bazei de date

Numele userului

Tipul lui (daca este user de sql sau user de windows)

Permisiunea

Tipul rolului prin care a primit acea permisiune

Solutie

— exec BD_AllDatabasePermissionsForAUser_prc ‘test’

alter procedure BD_AllDatabasePermissionsForAUser_prc
@user varchar(max)

DECLARE @dbname VARCHAR(50)
DECLARE @statement NVARCHAR(MAX)

/*
if exists (select top 1 1 from sys.sysobjects where name =’PERMISIUNI_BD’)
drop table PERMISIUNI_BD

create table PERMISIUNI_BD
(
ServerName varchar(max),
dbname varchar(max),
UserName varchar(max),
TypeOfLogin varchar(max),
PermissionLevel varchar(max),
TypeOfRole varchar(max)
)
*/

DECLARE db_cursor CURSOR
LOCAL FAST_FORWARD
FOR
SELECT name
FROM MASTER.sys.databases
WHERE name NOT IN (‘model’,’tempdb’)
AND state_desc=’online’
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @statement = ‘use ‘+@dbname +’;’+ ‘ SELECT

ServerName=@@servername, dbname=db_name(db_id()),p.name as UserName, p.type_desc as TypeOfLogin, pp.name as PermissionLevel, pp.type_desc as TypeOfRole

FROM sys.database_role_members roles

JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id

JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
where p.name=”’+@user+””

EXEC sp_executesql @statement

FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor

Tip solutie

Permanent

Follow Us