sql | SQL Server‎ > ‎sqlsrv | T-SQL‎ > ‎

tsql | List server, db, principal, object, permission type, permission status


DECLARE @DBNAME NVARCHAR(MAX)
declare @nur table (
[servername] nvarchar(max), 
[dbname] nvarchar(max), 
GRANTEE_ID BIGINT,
GRANTEE_NAME NVARCHAR(MAX),
GRANTEE_TYPE NVARCHAR(MAX),
GRANTOR_ID BIGINT,
GRANTOR_NAME NVARCHAR(MAX),
OBJECT_ID BIGINT,
OBJECT_NAME NVARCHAR(MAX),
OBJECT_TYPE NVARCHAR(MAX),
permission_name NVARCHAR(MAX),
state_desc NVARCHAR(MAX)
);

DECLARE @SQL NVARCHAR(MAX)

DECLARE __DB_CUR__ CURSOR FOR
SELECT D.NAME FROM SYS.DATABASES D

OPEN __DB_CUR__

FETCH NEXT FROM __DB_CUR__ INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL =
'SELECT 
@@SERVERNAME SERVERNAME
, ''' + @DBNAME + ''' DBNAME
, P.grantee_principal_id GRANTEE_ID
, PC.name GRANTEE_NAME
, PC.TYPE_DESC GRANTEE_TYPE
, P.grantor_principal_id GRANTOR_ID
, PCR.name GRANTOR_NAME
, P.major_id OBJECT_ID
, O.name OBJECT_NAME 
, O.type_desc OBJECT_TYPE
, P.permission_name
, P.state_desc
FROM [' + @DBNAME + '].SYS.database_permissions P
INNER JOIN [' + @DBNAME + '].SYS.objects O
ON P.major_id = O.object_id
INNER JOIN [' + @DBNAME + '].SYS.database_principals PC
ON P.grantee_principal_id = PC.principal_id
INNER JOIN [' + @DBNAME + '].SYS.database_principals PCR
ON P.grantor_principal_id = PCR.principal_id
ORDER BY GRANTEE_ID, OBJECT_NAME'
INSERT INTO @nur
EXEC (@SQL)

FETCH NEXT FROM __DB_CUR__ INTO @DBNAME
END

CLOSE __DB_CUR__
DEALLOCATE __DB_CUR__


SELECT * FROM @nur


Comments