usuarios usuario una tipos sobre servidor rol permisos nivel datos dar asignar sql-server sql-server-2005 sql-server-2008

tipos - Consulta de SQL Server para encontrar todos los permisos/acceso para todos los usuarios en una base de datos



roles de servidor sql server (9)

Me gustaría escribir una consulta en un sql 2008 que informe a todos los usuarios que tienen acceso a una base de datos específica, u objetos dentro de la base de datos tales como tablas, vistas y procedimientos almacenados, ya sea directamente o debido a roles, etc. el informe se usaría con fines de auditoría de seguridad. No estoy seguro si alguien tiene una consulta que se ajuste a mis necesidades por completo, pero espero que sea algo que me dé un buen comienzo. Ya sea en el año 2008, 2005 o 2000, probablemente pueda convertir según sea necesario.


Aquí hay una versión completa de la consulta de Jeremy de agosto de 2011 con los cambios sugeridos por Brad (octubre de 2011) y iw.kuchin (mayo de 2012) incorporados:

  1. Brad: corrige [ObjectType] y [ObjectName] para los esquemas.
  2. iw.kuchin: para [ObjectType] es mejor usar obj.type_desc solo para la clase de permiso OBJECT_OR_COLUMN . Para todos los demás casos use perm.[class_desc] .
  3. iw.kuchin: maneja los permisos IMPERSONATE .
  4. iw.kuchin: Reemplace sys.login_token con sys.server_principals ya que también mostrará sys.server_principals sesión de SQL, no solo los de Windows.
  5. iw.kuchin: incluye grupos de Windows.
  6. iw.kuchin: Excluye usuarios sys e INFORMATION_SCHEMA.

Con suerte, esto le ahorra a alguien más una o dos horas de sus vidas. :)

/* Security Audit Report 1) List all access provisioned to a SQL user or Windows user/group directly 2) List all access provisioned to a SQL user or Windows user/group through a database or application role 3) List all access provisioned to the public role Columns Returned: UserType : Value will be either ''SQL User'', ''Windows User'', or ''Windows Group''. This reflects the type of user/group defined for the SQL Server account. DatabaseUserName: Name of the associated user as defined in the database user account. The database user may not be the same as the server user. LoginName : SQL or Windows/Active Directory user account. This could also be an Active Directory group. Role : The role name. This will be null if the associated permissions to the object are defined at directly on the user account, otherwise this will be the name of the role that the user is a member of. PermissionType : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ObjectType : Type of object the user/role is assigned permissions on. Examples could include USER_TABLE, SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. Schema : Name of the schema the object is in. ObjectName : Name of the object that the user/role is assigned permissions on. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ColumnName : Name of the column of the object that the user/role is assigned permissions on. This value is only populated if the object is a table, view or a table value function. */ --1) List all access provisioned to a SQL user or Windows user/group directly SELECT [UserType] = CASE princ.[type] WHEN ''S'' THEN ''SQL User'' WHEN ''U'' THEN ''Windows User'' WHEN ''G'' THEN ''Windows Group'' END, [DatabaseUserName] = princ.[name], [LoginName] = ulogin.[name], [Role] = NULL, [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = CASE perm.[class] WHEN 1 THEN obj.[type_desc] -- Schema-contained objects ELSE perm.[class_desc] -- Higher-level objects END, [Schema] = objschem.[name], [ObjectName] = CASE perm.[class] WHEN 3 THEN permschem.[name] -- Schemas WHEN 4 THEN imp.[name] -- Impersonations ELSE OBJECT_NAME(perm.[major_id]) -- General objects END, [ColumnName] = col.[name] FROM --Database user sys.database_principals AS princ --Login accounts LEFT JOIN sys.server_principals AS ulogin ON ulogin.[sid] = princ.[sid] --Permissions LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = princ.[principal_id] LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id] LEFT JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id] LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id] --Table columns LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id] AND col.[column_id] = perm.[minor_id] --Impersonations LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id] WHERE princ.[type] IN (''S'',''U'',''G'') -- No need for these system accounts AND princ.[name] NOT IN (''sys'', ''INFORMATION_SCHEMA'') UNION --2) List all access provisioned to a SQL user or Windows user/group through a database or application role SELECT [UserType] = CASE membprinc.[type] WHEN ''S'' THEN ''SQL User'' WHEN ''U'' THEN ''Windows User'' WHEN ''G'' THEN ''Windows Group'' END, [DatabaseUserName] = membprinc.[name], [LoginName] = ulogin.[name], [Role] = roleprinc.[name], [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = CASE perm.[class] WHEN 1 THEN obj.[type_desc] -- Schema-contained objects ELSE perm.[class_desc] -- Higher-level objects END, [Schema] = objschem.[name], [ObjectName] = CASE perm.[class] WHEN 3 THEN permschem.[name] -- Schemas WHEN 4 THEN imp.[name] -- Impersonations ELSE OBJECT_NAME(perm.[major_id]) -- General objects END, [ColumnName] = col.[name] FROM --Role/member associations sys.database_role_members AS members --Roles JOIN sys.database_principals AS roleprinc ON roleprinc.[principal_id] = members.[role_principal_id] --Role members (database users) JOIN sys.database_principals AS membprinc ON membprinc.[principal_id] = members.[member_principal_id] --Login accounts LEFT JOIN sys.server_principals AS ulogin ON ulogin.[sid] = membprinc.[sid] --Permissions LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id] LEFT JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id] LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id] --Table columns LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id] AND col.[column_id] = perm.[minor_id] --Impersonations LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id] WHERE membprinc.[type] IN (''S'',''U'',''G'') -- No need for these system accounts AND membprinc.[name] NOT IN (''sys'', ''INFORMATION_SCHEMA'') UNION --3) List all access provisioned to the public role, which everyone gets by default SELECT [UserType] = ''{All Users}'', [DatabaseUserName] = ''{All Users}'', [LoginName] = ''{All Users}'', [Role] = roleprinc.[name], [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = CASE perm.[class] WHEN 1 THEN obj.[type_desc] -- Schema-contained objects ELSE perm.[class_desc] -- Higher-level objects END, [Schema] = objschem.[name], [ObjectName] = CASE perm.[class] WHEN 3 THEN permschem.[name] -- Schemas WHEN 4 THEN imp.[name] -- Impersonations ELSE OBJECT_NAME(perm.[major_id]) -- General objects END, [ColumnName] = col.[name] FROM --Roles sys.database_principals AS roleprinc --Role permissions LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id] --All objects JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id] LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id] --Table columns LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id] AND col.[column_id] = perm.[minor_id] --Impersonations LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id] WHERE roleprinc.[type] = ''R'' AND roleprinc.[name] = ''public'' AND obj.[is_ms_shipped] = 0 ORDER BY [UserType], [DatabaseUserName], [LoginName], [Role], [Schema], [ObjectName], [ColumnName], [PermissionType], [PermissionState], [ObjectType]


Desde SQL Server 2005 en adelante, puede usar vistas del sistema para eso. Por ejemplo, esta consulta enumera todos los usuarios en una base de datos, con sus derechos:

select princ.name , princ.type_desc , perm.permission_name , perm.state_desc , perm.class_desc , object_name(perm.major_id) from sys.database_principals princ left join sys.database_permissions perm on perm.grantee_principal_id = princ.principal_id

Tenga en cuenta que un usuario también puede tener derechos a través de un rol. Por ejemplo, el rol db_data_reader otorga derechos de select en la mayoría de los objetos.


El procedimiento almacenado GetPermissions anterior es bueno, sin embargo, utiliza Sp_msforeachdb, lo que significa que se romperá si su instancia SQL tiene nombres de bases de datos que incluyen espacios o guiones y otros caracteres no recomendados. Creé una versión que evita el uso de Sp_msforeachdb y también incluye dos columnas que indican 1 - si el inicio de sesión es un inicio de sesión sysadmin (IsSysAdminLogin) y 2 - si el inicio de sesión es un usuario huérfano (IsEmptyRow).

USE [master] ; GO IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''dbo.uspGetPermissionsOfAllLogins_DBsOnColumns'') AND [type] in (N''P'',N''PC'') ) BEGIN DROP PROCEDURE dbo.uspGetPermissionsOfAllLogins_DBsOnColumns ; END GO CREATE PROCEDURE dbo.uspGetPermissionsOfAllLogins_DBsOnColumns AS SET NOCOUNT ON ; BEGIN TRY IF EXISTS ( SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N''[tempdb].dbo.[#permission]'') ) DROP TABLE #permission ; IF EXISTS ( SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N''[tempdb].dbo.[#userroles_kk]'') ) DROP TABLE #userroles_kk ; IF EXISTS ( SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N''[tempdb].dbo.[#rolemember_kk]'') ) DROP TABLE #rolemember_kk ; IF EXISTS ( SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N''[tempdb].dbo.[##db_name]'') ) DROP TABLE ##db_name ; DECLARE @db_name VARCHAR(255) ,@sql_text VARCHAR(MAX) ; SET @sql_text = ''CREATE TABLE ##db_name ( LoginUserName VARCHAR(MAX) ,'' ; DECLARE cursDBs CURSOR FOR SELECT [name] FROM sys.databases ORDER BY [name] ; OPEN cursDBs ; FETCH NEXT FROM cursDBs INTO @db_name WHILE @@FETCH_STATUS = 0 BEGIN SET @sql_text = @sql_text + QUOTENAME(@db_name) + '' VARCHAR(MAX) ,'' FETCH NEXT FROM cursDBs INTO @db_name END CLOSE cursDBs ; SET @sql_text = @sql_text + ''IsSysAdminLogin CHAR(1) ,IsEmptyRow CHAR(1) )'' --PRINT @sql_text EXEC (@sql_text) ; DEALLOCATE cursDBs ; DECLARE @RoleName VARCHAR(255) ,@UserName VARCHAR(255) ; CREATE TABLE #permission ( LoginUserName VARCHAR(255) ,databasename VARCHAR(255) ,[role] VARCHAR(255) ) ; DECLARE cursSysSrvPrinName CURSOR FOR SELECT [name] FROM sys.server_principals WHERE [type] IN ( ''S'', ''U'', ''G'' ) AND principal_id > 4 AND [name] NOT LIKE ''##%'' ORDER BY [name] ; OPEN cursSysSrvPrinName ; FETCH NEXT FROM cursSysSrvPrinName INTO @UserName WHILE @@FETCH_STATUS = 0 BEGIN CREATE TABLE #userroles_kk ( databasename VARCHAR(255) ,[role] VARCHAR(255) ) ; CREATE TABLE #rolemember_kk ( dbrole VARCHAR(255) ,membername VARCHAR(255) ,membersid VARBINARY(2048) ) ; DECLARE cursDatabases CURSOR FAST_FORWARD LOCAL FOR SELECT [name] FROM sys.databases ORDER BY [name] ; OPEN cursDatabases ; DECLARE @DBN VARCHAR(255) ,@sqlText NVARCHAR(4000) ; FETCH NEXT FROM cursDatabases INTO @DBN WHILE @@FETCH_STATUS = 0 BEGIN SET @sqlText = N''USE '' + QUOTENAME(@DBN) + ''; TRUNCATE TABLE #RoleMember_kk INSERT INTO #RoleMember_kk EXEC sp_helprolemember INSERT INTO #UserRoles_kk (DatabaseName,[Role]) SELECT db_name(),dbRole FROM #RoleMember_kk WHERE MemberName = '''''' + @UserName + '''''' '' --PRINT @sqlText ; EXEC sp_executesql @sqlText ; FETCH NEXT FROM cursDatabases INTO @DBN END CLOSE cursDatabases ; DEALLOCATE cursDatabases ; INSERT INTO #permission SELECT @UserName ''user'' ,b.name ,u.[role] FROM sys.sysdatabases b LEFT JOIN #userroles_kk u ON QUOTENAME(u.databasename) = QUOTENAME(b.name) ORDER BY 1 ; DROP TABLE #userroles_kk ; DROP TABLE #rolemember_kk ; FETCH NEXT FROM cursSysSrvPrinName INTO @UserName END CLOSE cursSysSrvPrinName ; DEALLOCATE cursSysSrvPrinName ; TRUNCATE TABLE ##db_name ; DECLARE @d1 VARCHAR(MAX) ,@d2 VARCHAR(MAX) ,@d3 VARCHAR(MAX) ,@ss VARCHAR(MAX) ; DECLARE cursPermisTable CURSOR FOR SELECT * FROM #permission ORDER BY 2 DESC ; OPEN cursPermisTable ; FETCH NEXT FROM cursPermisTable INTO @d1,@d2,@d3 WHILE @@FETCH_STATUS = 0 BEGIN IF NOT EXISTS ( SELECT 1 FROM ##db_name WHERE LoginUserName = @d1 ) BEGIN SET @ss = ''INSERT INTO ##db_name(LoginUserName) VALUES ('''''' + @d1 + '''''')'' EXEC (@ss) ; SET @ss = ''UPDATE ##db_name SET '' + @d2 + '' = '''''' + @d3 + '''''' WHERE LoginUserName = '''''' + @d1 + '''''''' EXEC (@ss) ; END ELSE BEGIN DECLARE @var NVARCHAR(MAX) ,@ParmDefinition NVARCHAR(MAX) ,@var1 NVARCHAR(MAX) ; SET @var = N''SELECT @var1 = '' + QUOTENAME(@d2) + '' FROM ##db_name WHERE LoginUserName = '''''' + @d1 + '''''''' ; SET @ParmDefinition = N''@var1 NVARCHAR(600) OUTPUT '' ; EXECUTE Sp_executesql @var,@ParmDefinition,@var1 = @var1 OUTPUT ; SET @var1 = ISNULL(@var1, '' '') ; SET @var = '' UPDATE ##db_name SET '' + @d2 + ''='''''' + @var1 + '' '' + @d3 + '''''' WHERE LoginUserName = '''''' + @d1 + '''''' '' ; EXEC (@var) ; END FETCH NEXT FROM cursPermisTable INTO @d1,@d2,@d3 END CLOSE cursPermisTable ; DEALLOCATE cursPermisTable ; UPDATE ##db_name SET IsSysAdminLogin = ''Y'' FROM ##db_name TT INNER JOIN dbo.syslogins SL ON TT.LoginUserName = SL.[name] WHERE SL.sysadmin = 1 ; DECLARE cursDNamesAsColumns CURSOR FAST_FORWARD LOCAL FOR SELECT [name] FROM tempdb.sys.columns WHERE OBJECT_ID = OBJECT_ID(''tempdb..##db_name'') AND [name] NOT IN (''LoginUserName'',''IsEmptyRow'') ORDER BY [name] ; OPEN cursDNamesAsColumns ; DECLARE @ColN VARCHAR(255) ,@tSQLText NVARCHAR(4000) ; FETCH NEXT FROM cursDNamesAsColumns INTO @ColN WHILE @@FETCH_STATUS = 0 BEGIN SET @tSQLText = N''UPDATE ##db_name SET IsEmptyRow = ''''N'''' WHERE IsEmptyRow IS NULL AND '' + QUOTENAME(@ColN) + '' IS NOT NULL ; '' --PRINT @tSQLText ; EXEC sp_executesql @tSQLText ; FETCH NEXT FROM cursDNamesAsColumns INTO @ColN END CLOSE cursDNamesAsColumns ; DEALLOCATE cursDNamesAsColumns ; UPDATE ##db_name SET IsEmptyRow = ''Y'' WHERE IsEmptyRow IS NULL ; UPDATE ##db_name SET IsSysAdminLogin = ''N'' FROM ##db_name TT INNER JOIN dbo.syslogins SL ON TT.LoginUserName = SL.[name] WHERE SL.sysadmin = 0 ; SELECT * FROM ##db_name ; DROP TABLE ##db_name ; DROP TABLE #permission ; END TRY BEGIN CATCH DECLARE @cursDBs_Status INT ,@cursSysSrvPrinName_Status INT ,@cursDatabases_Status INT ,@cursPermisTable_Status INT ,@cursDNamesAsColumns_Status INT ; SELECT @cursDBs_Status = CURSOR_STATUS(''GLOBAL'',''cursDBs'') ,@cursSysSrvPrinName_Status = CURSOR_STATUS(''GLOBAL'',''cursSysSrvPrinName'') ,@cursDatabases_Status = CURSOR_STATUS(''GLOBAL'',''cursDatabases'') ,@cursPermisTable_Status = CURSOR_STATUS(''GLOBAL'',''cursPermisTable'') ,@cursDNamesAsColumns_Status = CURSOR_STATUS(''GLOBAL'',''cursPermisTable'') ; IF @cursDBs_Status > -2 BEGIN CLOSE cursDBs ; DEALLOCATE cursDBs ; END IF @cursSysSrvPrinName_Status > -2 BEGIN CLOSE cursSysSrvPrinName ; DEALLOCATE cursSysSrvPrinName ; END IF @cursDatabases_Status > -2 BEGIN CLOSE cursDatabases ; DEALLOCATE cursDatabases ; END IF @cursPermisTable_Status > -2 BEGIN CLOSE cursPermisTable ; DEALLOCATE cursPermisTable ; END IF @cursDNamesAsColumns_Status > -2 BEGIN CLOSE cursDNamesAsColumns ; DEALLOCATE cursDNamesAsColumns ; END SELECT ErrorNum = ERROR_NUMBER(),ErrorMsg = ERROR_MESSAGE() ; END CATCH GO /* EXEC [master].dbo.uspGetPermissionsOfAllLogins_DBsOnColumns ; */


Esta es mi primera grieta en una consulta, basada en las sugerencias de Andomar. Esta consulta está destinada a proporcionar una lista de permisos que un usuario ha aplicado directamente a la cuenta de usuario o a través de funciones que tiene el usuario.

/* Security Audit Report 1) List all access provisioned to a sql user or windows user/group directly 2) List all access provisioned to a sql user or windows user/group through a database or application role 3) List all access provisioned to the public role Columns Returned: UserName : SQL or Windows/Active Directory user cccount. This could also be an Active Directory group. UserType : Value will be either ''SQL User'' or ''Windows User''. This reflects the type of user defined for the SQL Server user account. DatabaseUserName: Name of the associated user as defined in the database user account. The database user may not be the same as the server user. Role : The role name. This will be null if the associated permissions to the object are defined at directly on the user account, otherwise this will be the name of the role that the user is a member of. PermissionType : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ObjectType : Type of object the user/role is assigned permissions on. Examples could include USER_TABLE, SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ObjectName : Name of the object that the user/role is assigned permissions on. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ColumnName : Name of the column of the object that the user/role is assigned permissions on. This value is only populated if the object is a table, view or a table value function. */ --List all access provisioned to a sql user or windows user/group directly SELECT [UserName] = CASE princ.[type] WHEN ''S'' THEN princ.[name] WHEN ''U'' THEN ulogin.[name] COLLATE Latin1_General_CI_AI END, [UserType] = CASE princ.[type] WHEN ''S'' THEN ''SQL User'' WHEN ''U'' THEN ''Windows User'' END, [DatabaseUserName] = princ.[name], [Role] = null, [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = obj.type_desc,--perm.[class_desc], [ObjectName] = OBJECT_NAME(perm.major_id), [ColumnName] = col.[name] FROM --database user sys.database_principals princ LEFT JOIN --Login accounts sys.login_token ulogin on princ.[sid] = ulogin.[sid] LEFT JOIN --Permissions sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id] LEFT JOIN --Table columns sys.columns col ON col.[object_id] = perm.major_id AND col.[column_id] = perm.[minor_id] LEFT JOIN sys.objects obj ON perm.[major_id] = obj.[object_id] WHERE princ.[type] in (''S'',''U'') UNION --List all access provisioned to a sql user or windows user/group through a database or application role SELECT [UserName] = CASE memberprinc.[type] WHEN ''S'' THEN memberprinc.[name] WHEN ''U'' THEN ulogin.[name] COLLATE Latin1_General_CI_AI END, [UserType] = CASE memberprinc.[type] WHEN ''S'' THEN ''SQL User'' WHEN ''U'' THEN ''Windows User'' END, [DatabaseUserName] = memberprinc.[name], [Role] = roleprinc.[name], [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = obj.type_desc,--perm.[class_desc], [ObjectName] = OBJECT_NAME(perm.major_id), [ColumnName] = col.[name] FROM --Role/member associations sys.database_role_members members JOIN --Roles sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id] JOIN --Role members (database users) sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id] LEFT JOIN --Login accounts sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid] LEFT JOIN --Permissions sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] LEFT JOIN --Table columns sys.columns col on col.[object_id] = perm.major_id AND col.[column_id] = perm.[minor_id] LEFT JOIN sys.objects obj ON perm.[major_id] = obj.[object_id] UNION --List all access provisioned to the public role, which everyone gets by default SELECT [UserName] = ''{All Users}'', [UserType] = ''{All Users}'', [DatabaseUserName] = ''{All Users}'', [Role] = roleprinc.[name], [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = obj.type_desc,--perm.[class_desc], [ObjectName] = OBJECT_NAME(perm.major_id), [ColumnName] = col.[name] FROM --Roles sys.database_principals roleprinc LEFT JOIN --Role permissions sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] LEFT JOIN --Table columns sys.columns col on col.[object_id] = perm.major_id AND col.[column_id] = perm.[minor_id] JOIN --All objects sys.objects obj ON obj.[object_id] = perm.[major_id] WHERE --Only roles roleprinc.[type] = ''R'' AND --Only public role roleprinc.[name] = ''public'' AND --Only objects of ours, not the MS objects obj.is_ms_shipped = 0 ORDER BY princ.[Name], OBJECT_NAME(perm.major_id), col.[name], perm.[permission_name], perm.[state_desc], obj.type_desc--perm.[class_desc]


Guión impresionante Jeremy y colaboradores! ¡Gracias!

Tengo una tonelada de usuarios, por lo que ejecutar esto para todos los usuarios fue una pesadilla. No pude agregar comentarios, así que estoy publicando todo el script con los cambios. Agregué una cláusula variable + where para poder buscar cualquier cosa que coincida con hasta 5 caracteres en el nombre de usuario (o todos los usuarios cuando se deja en blanco). Nada especial, pero pensé que sería útil en algunos casos de uso.

DECLARE @p_userName NVARCHAR(5) = ''UName'' -- Specify up to five characters here (or none for all users) /* Security Audit Report 1) List all access provisioned to a sql user or windows user/group directly 2) List all access provisioned to a sql user or windows user/group through a database or application role 3) List all access provisioned to the public role Columns Returned: UserName : SQL or Windows/Active Directory user cccount. This could also be an Active Directory group. UserType : Value will be either ''SQL User'' or ''Windows User''. This reflects the type of user defined for the SQL Server user account. DatabaseUserName: Name of the associated user as defined in the database user account. The database user may not be the same as the server user. Role : The role name. This will be null if the associated permissions to the object are defined at directly on the user account, otherwise this will be the name of the role that the user is a member of. PermissionType : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT, DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ObjectType : Type of object the user/role is assigned permissions on. Examples could include USER_TABLE, SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ObjectName : Name of the object that the user/role is assigned permissions on. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ColumnName : Name of the column of the object that the user/role is assigned permissions on. This value is only populated if the object is a table, view or a table value function. */ DECLARE @userName NVARCHAR(4) = @p_UserName + ''%'' --List all access provisioned to a sql user or windows user/group directly SELECT [UserName] = CASE princ.[type] WHEN ''S'' THEN princ.[name] WHEN ''U'' THEN ulogin.[name] COLLATE Latin1_General_CI_AI END, [UserType] = CASE princ.[type] WHEN ''S'' THEN ''SQL User'' WHEN ''U'' THEN ''Windows User'' END, [DatabaseUserName] = princ.[name], [Role] = null, [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = obj.type_desc,--perm.[class_desc], [ObjectName] = OBJECT_NAME(perm.major_id), [ColumnName] = col.[name] FROM --database user sys.database_principals princ LEFT JOIN --Login accounts sys.login_token ulogin on princ.[sid] = ulogin.[sid] LEFT JOIN --Permissions sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id] LEFT JOIN --Table columns sys.columns col ON col.[object_id] = perm.major_id AND col.[column_id] = perm.[minor_id] LEFT JOIN sys.objects obj ON perm.[major_id] = obj.[object_id] WHERE princ.[type] in (''S'',''U'') AND princ.[name] LIKE @userName -- Added this line --CSLAGLE UNION --List all access provisioned to a sql user or windows user/group through a database or application role SELECT [UserName] = CASE memberprinc.[type] WHEN ''S'' THEN memberprinc.[name] WHEN ''U'' THEN ulogin.[name] COLLATE Latin1_General_CI_AI END, [UserType] = CASE memberprinc.[type] WHEN ''S'' THEN ''SQL User'' WHEN ''U'' THEN ''Windows User'' END, [DatabaseUserName] = memberprinc.[name], [Role] = roleprinc.[name], [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = obj.type_desc,--perm.[class_desc], [ObjectName] = OBJECT_NAME(perm.major_id), [ColumnName] = col.[name] FROM --Role/member associations sys.database_role_members members JOIN --Roles sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id] JOIN --Role members (database users) sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id] LEFT JOIN --Login accounts sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid] LEFT JOIN --Permissions sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] LEFT JOIN --Table columns sys.columns col on col.[object_id] = perm.major_id AND col.[column_id] = perm.[minor_id] LEFT JOIN sys.objects obj ON perm.[major_id] = obj.[object_id] WHERE memberprinc.[name] LIKE @userName -- Added this line --CSLAGLE UNION --List all access provisioned to the public role, which everyone gets by default SELECT [UserName] = ''{All Users}'', [UserType] = ''{All Users}'', [DatabaseUserName] = ''{All Users}'', [Role] = roleprinc.[name], [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = obj.type_desc,--perm.[class_desc], [ObjectName] = OBJECT_NAME(perm.major_id), [ColumnName] = col.[name] FROM --Roles sys.database_principals roleprinc LEFT JOIN --Role permissions sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] LEFT JOIN --Table columns sys.columns col on col.[object_id] = perm.major_id AND col.[column_id] = perm.[minor_id] JOIN --All objects sys.objects obj ON obj.[object_id] = perm.[major_id] WHERE --Only roles roleprinc.[type] = ''R'' AND --Only public role roleprinc.[name] = ''public'' AND --Only objects of ours, not the MS objects obj.is_ms_shipped = 0 ORDER BY princ.[Name], OBJECT_NAME(perm.major_id), col.[name], perm.[permission_name], perm.[state_desc], obj.type_desc--perm.[class_desc]


Las otras respuestas que he visto pierden algunos permisos que son posibles en la base de datos. La primera consulta en el siguiente código obtendrá el permiso de nivel de base de datos para todo lo que no sea un objeto del sistema. Genera las declaraciones adecuadas de GRANT también. La segunda consulta obtiene todas las funciones de rol.

Esto debe ejecutarse para cada base de datos, pero es demasiado largo para usarlo con sp_MSforeachdb. Si desea hacerlo, deberá agregarlo a la base de datos maestra como un procedimiento almacenado en el sistema.

Para cubrir todas las posibilidades, también debe tener un script que verifique los permisos de nivel de servidor.

SELECT DB_NAME() AS database_name , class , class_desc , major_id , minor_id , grantee_principal_id , grantor_principal_id , databasepermissions.type , permission_name , STATE , state_desc , granteedatabaseprincipal.name AS grantee_name , granteedatabaseprincipal.type_desc AS grantee_type_desc , granteeserverprincipal.name AS grantee_principal_name , granteeserverprincipal.type_desc AS grantee_principal_type_desc , grantor.name AS grantor_name , granted_on_name , permissionstatement + N'' TO '' + QUOTENAME(granteedatabaseprincipal.name) + CASE WHEN STATE = N''W'' THEN N'' WITH GRANT OPTION'' ELSE N'''' END AS permissionstatement FROM ( SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(CONVERT(NVARCHAR(MAX), DB_NAME())) AS granted_on_name , CASE WHEN sys.database_permissions.state = N''W'' THEN N''GRANT'' ELSE sys.database_permissions.state_desc END + N'' '' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS permissionstatement FROM sys.database_permissions WHERE (sys.database_permissions.class = 0) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.schemas.name) + N''.'' + QUOTENAME(sys.objects.name) AS granted_on_name , CASE WHEN sys.database_permissions.state = N''W'' THEN N''GRANT'' ELSE sys.database_permissions.state_desc END + N'' '' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N'' ON '' + QUOTENAME(sys.schemas.name) + N''.'' + QUOTENAME(sys.objects.name) + COALESCE(N'' ('' + QUOTENAME(sys.columns.name) + N'')'', N'''') AS permissionstatement FROM sys.database_permissions INNER JOIN sys.objects ON sys.objects.object_id = sys.database_permissions.major_id INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.objects.schema_id LEFT OUTER JOIN sys.columns ON sys.columns.object_id = sys.database_permissions.major_id AND sys.columns.column_id = sys.database_permissions.minor_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 1) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.schemas.name) AS granted_on_name , CASE WHEN sys.database_permissions.state = N''W'' THEN N''GRANT'' ELSE sys.database_permissions.state_desc END + N'' '' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N'' ON SCHEMA::'' + QUOTENAME(sys.schemas.name) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 3) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(targetPrincipal.name) AS granted_on_name , CASE WHEN sys.database_permissions.state = N''W'' THEN N''GRANT'' ELSE sys.database_permissions.state_desc END + N'' '' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N'' ON '' + targetPrincipal.type_desc + N''::'' + QUOTENAME(targetPrincipal.name) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.database_principals AS targetPrincipal ON targetPrincipal.principal_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 4) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.assemblies.name) AS granted_on_name , CASE WHEN sys.database_permissions.state = N''W'' THEN N''GRANT'' ELSE sys.database_permissions.state_desc END + N'' '' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N'' ON ASSEMBLY::'' + QUOTENAME(sys.assemblies.name) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.assemblies ON sys.assemblies.assembly_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 5) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.types.name) AS granted_on_name , CASE WHEN sys.database_permissions.state = N''W'' THEN N''GRANT'' ELSE sys.database_permissions.state_desc END + N'' '' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N'' ON TYPE::'' + QUOTENAME(sys.types.name) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.types ON sys.types.user_type_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 6) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.types.name) AS granted_on_name , CASE WHEN sys.database_permissions.state = N''W'' THEN N''GRANT'' ELSE sys.database_permissions.state_desc END + N'' '' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N'' ON TYPE::'' + QUOTENAME(sys.types.name) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.types ON sys.types.user_type_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 6) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.xml_schema_collections.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name , CASE WHEN sys.database_permissions.state = N''W'' THEN N''GRANT'' ELSE sys.database_permissions.state_desc END + N'' '' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N'' ON XML SCHEMA COLLECTION::'' + QUOTENAME(sys.xml_schema_collections.name) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.xml_schema_collections ON sys.xml_schema_collections.xml_collection_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 10) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.service_message_types.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name , CASE WHEN sys.database_permissions.state = N''W'' THEN N''GRANT'' ELSE sys.database_permissions.state_desc END + N'' '' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N'' ON MESSAGE TYPE::'' + QUOTENAME(sys.service_message_types.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.service_message_types ON sys.service_message_types.message_type_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 15) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.service_contracts.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name , CASE WHEN sys.database_permissions.state = N''W'' THEN N''GRANT'' ELSE sys.database_permissions.state_desc END + N'' '' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N'' ON CONTRACT::'' + QUOTENAME(sys.service_contracts.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.service_contracts ON sys.service_contracts.service_contract_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 16) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.services.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name , CASE WHEN sys.database_permissions.state = N''W'' THEN N''GRANT'' ELSE sys.database_permissions.state_desc END + N'' '' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N'' ON SERVICE::'' + QUOTENAME(sys.services.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.services ON sys.services.service_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 17) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.remote_service_bindings.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name , CASE WHEN sys.database_permissions.state = N''W'' THEN N''GRANT'' ELSE sys.database_permissions.state_desc END + N'' '' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N'' ON REMOTE SERVICE BINDING::'' + QUOTENAME(sys.remote_service_bindings.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.remote_service_bindings ON sys.remote_service_bindings.remote_service_binding_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 18) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.routes.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name , CASE WHEN sys.database_permissions.state = N''W'' THEN N''GRANT'' ELSE sys.database_permissions.state_desc END + N'' '' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N'' ON ROUTE::'' + QUOTENAME(sys.routes.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.routes ON sys.routes.route_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 19) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.symmetric_keys.name) AS granted_on_name , CASE WHEN sys.database_permissions.state = N''W'' THEN N''GRANT'' ELSE sys.database_permissions.state_desc END + N'' '' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N'' ON ASYMMETRIC KEY::'' + QUOTENAME(sys.symmetric_keys.name) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.symmetric_keys ON sys.symmetric_keys.symmetric_key_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 24) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.certificates.name) AS granted_on_name , CASE WHEN sys.database_permissions.state = N''W'' THEN N''GRANT'' ELSE sys.database_permissions.state_desc END + N'' '' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N'' ON CERTIFICATE::'' + QUOTENAME(sys.certificates.name) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.certificates ON sys.certificates.certificate_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 25) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.asymmetric_keys.name) AS granted_on_name , CASE WHEN sys.database_permissions.state = N''W'' THEN N''GRANT'' ELSE sys.database_permissions.state_desc END + N'' '' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N'' ON ASYMMETRIC KEY::'' + QUOTENAME(sys.asymmetric_keys.name) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.asymmetric_keys ON sys.asymmetric_keys.asymmetric_key_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 26) ) AS databasepermissions INNER JOIN sys.database_principals AS granteedatabaseprincipal ON granteedatabaseprincipal.principal_id = grantee_principal_id LEFT OUTER JOIN sys.server_principals AS granteeserverprincipal ON granteeserverprincipal.sid = granteedatabaseprincipal.sid INNER JOIN sys.database_principals AS grantor ON grantor.principal_id = grantor_principal_id ORDER BY grantee_name, granted_on_name SELECT roles.name AS role_name , roles.principal_id , roles.type AS role_type , roles.type_desc AS role_type_desc , roles.is_fixed_role AS role_is_fixed_role , memberdatabaseprincipal.name AS member_name , memberdatabaseprincipal.principal_id AS member_principal_id , memberdatabaseprincipal.type AS member_type , memberdatabaseprincipal.type_desc AS member_type_desc , memberdatabaseprincipal.is_fixed_role AS member_is_fixed_role , memberserverprincipal.name AS member_principal_name , memberserverprincipal.type_desc member_principal_type_desc , N''ALTER ROLE '' + QUOTENAME(roles.name) + N'' ADD MEMBER '' + QUOTENAME(memberdatabaseprincipal.name) AS AddRoleMembersStatement FROM sys.database_principals AS roles INNER JOIN sys.database_role_members ON sys.database_role_members.role_principal_id = roles.principal_id INNER JOIN sys.database_principals AS memberdatabaseprincipal ON memberdatabaseprincipal.principal_id = sys.database_role_members.member_principal_id LEFT OUTER JOIN sys.server_principals AS memberserverprincipal ON memberserverprincipal.sid = memberdatabaseprincipal.sid ORDER BY role_name , member_name


No puedo comentar sobre la respuesta aceptada, así que añadiré algunos comentarios aquí:

  • Secundo a Brad en cuestión de esquemas. Desde MS, la tabla sys.objects referencia contiene solo objetos con un esquema de ámbito. Entonces, para obtener información sobre objetos de "nivel superior" (es decir, esquemas en nuestro caso), debe usar la tabla sys.schemas .
  • Para [ObjectType] , es mejor usar obj.type_desc solo para la clase de permiso OBJECT_OR_COLUMN . Para todos los demás casos use perm.[class_desc]
  • Otro tipo de permiso que no se maneja tan bien con esta consulta es IMPERSONATE . Para obtener información sobre las suplantaciones, perm.major_id = imp.principal_id LEFT JOIN con sys.database_principals en perm.major_id = imp.principal_id
  • Con mi experiencia, es mejor reemplazar sys.login_token con sys.server_principals ya que también mostrará los inicios de sesión de SQL, no solo los de Windows.
  • Se debe agregar ''G'' a los tipos principales permitidos para permitir grupos de Windows
  • Además, se puede excluir a los usuarios sys y INFORMATION_SCHEMA de la tabla resultante, ya que estos usuarios solo se utilizan para el servicio.

Publicaré la primera secuencia de comandos con todas las correcciones propuestas, otras partes también deberían cambiarse:

SELECT [UserName] = ulogin.[name], [UserType] = CASE princ.[type] WHEN ''S'' THEN ''SQL User'' WHEN ''U'' THEN ''Windows User'' WHEN ''G'' THEN ''Windows Group'' END, [DatabaseUserName] = princ.[name], [Role] = null, [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = CASE perm.[class] WHEN 1 THEN obj.type_desc -- Schema-contained objects ELSE perm.[class_desc] -- Higher-level objects END, [ObjectName] = CASE perm.[class] WHEN 1 THEN OBJECT_NAME(perm.major_id) -- General objects WHEN 3 THEN schem.[name] -- Schemas WHEN 4 THEN imp.[name] -- Impersonations END, [ColumnName] = col.[name] FROM --database user sys.database_principals princ LEFT JOIN --Login accounts sys.server_principals ulogin on princ.[sid] = ulogin.[sid] LEFT JOIN --Permissions sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id] LEFT JOIN --Table columns sys.columns col ON col.[object_id] = perm.major_id AND col.[column_id] = perm.[minor_id] LEFT JOIN sys.objects obj ON perm.[major_id] = obj.[object_id] LEFT JOIN sys.schemas schem ON schem.[schema_id] = perm.[major_id] LEFT JOIN sys.database_principals imp ON imp.[principal_id] = perm.[major_id] WHERE princ.[type] IN (''S'',''U'',''G'') AND -- No need for these system accounts princ.[name] NOT IN (''sys'', ''INFORMATION_SCHEMA'')


Unfortunately I couldn''t comment on the Sean Rose post due to insufficient reputation, however I had to amend the "public" role portion of the script as it didn''t show SCHEMA-scoped permissions due to the (INNER) JOIN against sys.objects. After that was changed to a LEFT JOIN I further had to amend the WHERE-clause logic to omit system objects. My amended query for the public perms is below.

--3) List all access provisioned to the public role, which everyone gets by default SELECT @@servername ServerName , db_name() DatabaseName , [UserType] = ''{All Users}'', [DatabaseUserName] = ''{All Users}'', [LoginName] = ''{All Users}'', [Role] = roleprinc.[name], [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = CASE perm.[class] WHEN 1 THEN obj.[type_desc] -- Schema-contained objects ELSE perm.[class_desc] -- Higher-level objects END, [Schema] = objschem.[name], [ObjectName] = CASE perm.[class] WHEN 3 THEN permschem.[name] -- Schemas WHEN 4 THEN imp.[name] -- Impersonations ELSE OBJECT_NAME(perm.[major_id]) -- General objects END, [ColumnName] = col.[name] FROM --Roles sys.database_principals AS roleprinc --Role permissions LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id] --All objects LEFT JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id] LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id] --Table columns LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id] AND col.[column_id] = perm.[minor_id] --Impersonations LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id] WHERE roleprinc.[type] = ''R'' AND roleprinc.[name] = ''public'' AND isnull(obj.[is_ms_shipped], 0) = 0 AND isnull(object_schema_name(perm.[major_id]), '''') <> ''sys'' ORDER BY [UserType], [DatabaseUserName], [LoginName], [Role], [Schema], [ObjectName], [ColumnName], [PermissionType], [PermissionState], [ObjectType]


CREATE PROCEDURE Get_permission AS DECLARE @db_name VARCHAR(200), @sql_text VARCHAR(max) SET @sql_text=''Create table ##db_name (user_name varchar(max),'' DECLARE db_cursor CURSOR FOR SELECT name FROM sys.databases OPEN db_cursor FETCH next FROM db_cursor INTO @db_name WHILE @@FETCH_STATUS = 0 BEGIN SET @sql_text=@sql_text + @db_name + '' varchar(max),'' FETCH next FROM db_cursor INTO @db_name END CLOSE db_cursor SET @sql_text=@sql_text + ''Server_perm varchar(max))'' EXEC (@sql_text) DEALLOCATE db_cursor DECLARE @RoleName VARCHAR(50) DECLARE @UserName VARCHAR(50) DECLARE @CMD VARCHAR(1000) CREATE TABLE #permission ( user_name VARCHAR(50), databasename VARCHAR(50), role VARCHAR(50) ) DECLARE longspcur CURSOR FOR SELECT name FROM sys.server_principals WHERE type IN ( ''S'', ''U'', ''G'' ) AND principal_id > 4 AND name NOT LIKE ''##%'' AND name <> ''NT AUTHORITY/SYSTEM'' AND name <> ''ONDEMAND/Administrator'' AND name NOT LIKE ''steel%'' OPEN longspcur FETCH next FROM longspcur INTO @UserName WHILE @@FETCH_STATUS = 0 BEGIN CREATE TABLE #userroles_kk ( databasename VARCHAR(50), role VARCHAR(50) ) CREATE TABLE #rolemember_kk ( dbrole VARCHAR(100), membername VARCHAR(100), membersid VARBINARY(2048) ) SET @CMD = ''use ? truncate table #RoleMember_kk insert into #RoleMember_kk exec sp_helprolemember insert into #UserRoles_kk (DatabaseName, Role) select db_name(), dbRole from #RoleMember_kk where MemberName = '''''' + @UserName + '''''''' EXEC Sp_msforeachdb @CMD INSERT INTO #permission SELECT @UserName ''user'', b.name, u.role FROM sys.sysdatabases b LEFT OUTER JOIN #userroles_kk u ON u.databasename = b.name --and u.Role=''db_owner'' ORDER BY 1 DROP TABLE #userroles_kk; DROP TABLE #rolemember_kk; FETCH next FROM longspcur INTO @UserName END CLOSE longspcur DEALLOCATE longspcur TRUNCATE TABLE ##db_name DECLARE @d1 VARCHAR(max), @d2 VARCHAR(max), @d3 VARCHAR(max), @ss VARCHAR(max) DECLARE perm_cur CURSOR FOR SELECT * FROM #permission ORDER BY 2 DESC OPEN perm_cur FETCH next FROM perm_cur INTO @d1, @d2, @d3 WHILE @@FETCH_STATUS = 0 BEGIN IF NOT EXISTS(SELECT 1 FROM ##db_name WHERE user_name = @d1) BEGIN SET @ss=''insert into ##db_name(user_name) values ('''''' + @d1 + '''''')'' EXEC (@ss) SET @ss=''update ##db_name set '' + @d2 + ''='''''' + @d3 + '''''' where user_name='''''' + @d1 + '''''''' EXEC (@ss) END ELSE BEGIN DECLARE @var NVARCHAR(max), @ParmDefinition NVARCHAR(max), @var1 NVARCHAR(max) SET @var = N''select @var1='' + @d2 + '' from ##db_name where USER_NAME='''''' + @d1 + ''''''''; SET @ParmDefinition = N''@var1 nvarchar(300) OUTPUT''; EXECUTE Sp_executesql @var, @ParmDefinition, @var1=@var1 output; SET @var1=Isnull(@var1, '' '') SET @var= '' update ##db_name set '' + @d2 + ''='''''' + @var1 + '' '' + @d3 + '''''' where user_name='''''' + @d1 + '''''' '' EXEC (@var) END FETCH next FROM perm_cur INTO @d1, @d2, @d3 END CLOSE perm_cur DEALLOCATE perm_cur SELECT * FROM ##db_name DROP TABLE ##db_name DROP TABLE #permission