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 |
sql | SQL Server > sqlsrv | T-SQL >