Главная » Вопросы и ответы » sql-server

Запрос SQL Server для поиска всех разрешений/доступа для всех пользователей в базе данных

На чтение 53 мин Просмотров 3 Опубликовано
Вопрос:Я хотел бы написать запрос на sql 2008, который будет сообщать обо всех пользователях, имеющих доступ к конкретной базе данных, или объектам в базе данных, таким как таблицы, представления и хранимые процедуры, напрямую или из-за ролей, и т.д. Этот отчет будет использоваться для целей аудита безопасности. Не уверен, что у кого-то есть запрос, который полностью

Вопрос:

Я хотел бы написать запрос на sql 2008, который будет сообщать обо всех пользователях, имеющих доступ к конкретной базе данных, или объектам в базе данных, таким как таблицы, представления и хранимые процедуры, напрямую или из-за ролей, и т.д. Этот отчет будет использоваться для целей аудита безопасности. Не уверен, что у кого-то есть запрос, который полностью удовлетворит мои потребности, но, надеюсь, что-то, что даст мне хорошее начало. Любой sql 2008, 2005 или 2000 будет делать, я могу, возможно, преобразовать по мере необходимости.

Лучший ответ:

Это мой первый треск на запрос, основанный на предложениях Andomar. Этот запрос предназначен для предоставления списка разрешений, которые пользователь применял либо непосредственно к учетной записи пользователя, либо через роли, которые пользователь имеет.

/* 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 account. 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] Ответ №1

Вот полная версия запроса Jeremy Aug 2011 с изменениями, предложенными Brad (октябрь 2011 г.) и iw.kuchin(май 2012 г.):

  • Brad: исправить [ObjectType] и [ObjectName] для схем.
  • iw.kuchin: для [ObjectType] лучше использовать obj.type_desc только для класса разрешений OBJECT_OR_COLUMN. Для всех остальных случаев используйте perm.[class_desc].
  • iw.kuchin: разрешить IMPERSONATE разрешения.
  • iw.kuchin: Замените sys.login_token на sys.server_principals, поскольку он покажет также SQL-логины, а не только Windows.
  • iw.kuchin: включить группы Windows.
  • iw.kuchin: исключить пользователей sys и INFORMATION_SCHEMA.

Надеюсь, это спасет кого-то еще час или две из их жизней. 🙂

/* 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] Ответ №2

В SQL Server 2005 вы можете использовать для этого системные представления. Например, в этом запросе перечислены все пользователи в базе данных с их правами:

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

Имейте в виду, что пользователь может иметь права и через роль. Например, роль db_data_reader предоставляет права select для большинства объектов.

Ответ №3

Не могу комментировать принятый ответ, поэтому я добавлю несколько комментариев здесь:

  • Я второй Брэд по вопросам схемы. Из таблицы MS sys.objects таблица содержит только объекты с областью схемы. Поэтому, чтобы получить информацию о объектах “более высокого уровня” (например, в нашем случае), вам нужно использовать таблицу sys.schemas.
  • Для [ObjectType] лучше использовать obj.type_desc только для класса разрешений OBJECT_OR_COLUMN. Для всех остальных случаев используйте perm.[class_desc]
  • Другим типом разрешений, который так плохо обрабатывается с этим запросом, является IMPERSONATE. Чтобы получить информацию о олицетворениях, нужно LEFT JOIN с sys.database_principals на perm.major_id = imp.principal_id
  • По моему опыту лучше заменить sys.login_token на sys.server_principals, поскольку он покажет также SQL-логины, а не только Windows
  • Следует добавить ‘G’, чтобы разрешенные типы-типы разрешали группы Windows
  • Кроме того, из итоговой таблицы исключаются пользователи sys и INFORMATION_SCHEMA, поскольку эти пользователи используются только для службы

Я выложу первую часть script со всеми предлагаемыми исправлениями, другие части также должны быть изменены:

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’) Ответ №4

Awesome script Джереми и авторы! Спасибо!

У меня есть тонна пользователей, поэтому запуск этого для всех пользователей был кошмаром. Я не мог добавлять комментарии, поэтому я публикую все script с изменениями. Я добавил предложение variable + where, чтобы я мог искать все, что соответствовало бы до 5 символов в имени пользователя (или всех пользователей, когда они были пустыми). Ничего особенного, но я подумал, что это будет полезно в некоторых случаях.

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] Ответ №5

Другие ответы, которые я видел, пропускают некоторые разрешения, которые возможны в базе данных. Первый запрос в приведенном ниже коде получит разрешение на уровне базы данных для всего, которое не является системным объектом. Он также генерирует соответствующие заявления GRANT. Второй запрос получает все роли.

Это нужно запускать для каждой базы данных, но слишком долго для использования с sp_MSforeachdb. Если вы хотите сделать это, вам придется добавить его в основную базу данных в качестве хранимой процедуры системы.

Чтобы охватить все возможности, вам также необходимо иметь script, который проверяет разрешения на уровне сервера.

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 Ответ №6CREATE 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 AUTHORITYSYSTEM’ AND name <> ‘ONDEMANDAdministrator’ 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 Ответ №7

Хранимая процедура GetPermissions выше хороша, однако использует Sp_msforeachdb, что означает, что она сломается, если ваш экземпляр SQL имеет любые имена баз данных, которые включают пробелы или тире и другие символы, отличные от лучших практик. Я создал версию, которая позволяет избежать использования Sp_msforeachdb, а также включает в себя два столбца, которые указывают 1 – если Login – это логин sysadmin (IsSysAdminLogin) и 2 – если логин является пользователем-сиротой (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 ; */ Ответ №8

Вот моя версия, адаптированная от других. Сейчас я потратил 30 минут, пытаясь вспомнить, как я это придумал, и ответ @Jeremy, кажется, является основным источником вдохновения. Я не хотел обновлять ответ Джереми, на всякий случай, если я представил ошибки, поэтому я публикую свою версию здесь.

Я предлагаю объединить полный сценарий с вдохновением, взятым из Kenneth Fisher T-SQL вторник: какие разрешения имеет конкретный пользователь? : Это позволит вам отвечать на вопросы соответствия/аудита снизу вверх, а не сверху вниз.

EXECUTE AS LOGIN = ‘<loginname>’ SELECT token.name AS GroupNames FROM sys.login_token token JOIN sys.server_principals grp ON token.sid = grp.sid WHERE token.[type] = ‘WINDOWS GROUP’ AND grp.[type] = ‘G’ REVERT

Чтобы понять, что это покрывает, рассмотрим ContosoDB_AdventureWorks_Accounting Windows AD Group с членом ContosoJohn.Doe. John.Doe проходит аутентификацию в AdventureWorks с помощью сервера_принципов ContosoDB_AdventureWorks_Logins Windows AD Group. Если кто-то спросит вас: “Какие разрешения есть у John.Doe?”, Вы не сможете ответить на этот вопрос только с помощью приведенного ниже сценария. Затем необходимо выполнить итерацию по каждой строке, возвращаемой приведенным ниже сценарием, и присоединить его к указанному выше сценарию. (Вам также может понадобиться нормализовать устаревшие значения name посмотрев SID в вашем поставщике Active Directory.)

Вот сценарий, без включения такой логики обратного просмотра.

/* —Script source found at : http://stackoverflow.com/a/7059579/1387418 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 account. 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. PrinciaplUserName: if UserName is not blank, then UserName else DatabaseUserName PrincipalType : Possible values are ‘SQL User’, ‘Windows User’, ‘Database Role’, ‘Windows Group’ 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 @HideDatabaseDiagrams BIT = 1; —List all access provisioned to a sql user or windows user/group directly SELECT [UserName] = CASE dbprinc.[type] WHEN ‘S’ THEN dbprinc.[name] — SQL User WHEN ‘U’ THEN sprinc.[name] — Windows User WHEN ‘R’ THEN NULL — Database Role WHEN ‘G’ THEN NULL — Windows Group ELSE NULL END, [UserType] = CASE dbprinc.[type] WHEN ‘S’ THEN ‘SQL User’ WHEN ‘U’ THEN ‘Windows User’ WHEN ‘R’ THEN NULL — Database Role WHEN ‘G’ THEN NULL — Windows Group ELSE dbprinc.[type] END, [PrincipalUserName] = COALESCE( CASE dbprinc.[type] WHEN ‘S’ THEN dbprinc.[name] — SQL User WHEN ‘U’ THEN sprinc.[name] — Windows User WHEN ‘R’ THEN NULL — Database Role WHEN ‘G’ THEN NULL — Windows Group ELSE NULL END, dbprinc.[name] ), [PrincipalType] = CASE dbprinc.[type] WHEN ‘S’ THEN ‘SQL User’ WHEN ‘U’ THEN ‘Windows User’ WHEN ‘R’ THEN ‘Database Role’ WHEN ‘G’ THEN ‘Windows Group’ END, [DatabaseUserName] = dbprinc.[name], [Role] = null, [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = obj.[type_desc],—perm.[class_desc], [ObjectSchema] = OBJECT_SCHEMA_NAME(perm.major_id), [ObjectName] = OBJECT_NAME(perm.major_id), [ColumnName] = col.[name] FROM —database user sys.database_principals dbprinc LEFT JOIN —Login accounts sys.server_principals sprinc on dbprinc.[sid] = sprinc.[sid] LEFT JOIN —Permissions sys.database_permissions perm ON perm.[grantee_principal_id] = dbprinc.[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 dbprinc.[type] in (‘S’,’U’) AND CASE WHEN @HideDatabaseDiagrams = 1 AND dbprinc.[name] = ‘guest’ AND ( ( obj.type_desc = ‘SQL_SCALAR_FUNCTION’ AND OBJECT_NAME(perm.major_id) = ‘fn_diagramobjects’ ) OR ( obj.type_desc = ‘SQL_STORED_PROCEDURE’ AND OBJECT_NAME(perm.major_id) IN ( N’sp_alterdiagram’, N’sp_creatediagram’, N’sp_dropdiagram’, N’sp_helpdiagramdefinition’, N’sp_helpdiagrams’, N’sp_renamediagram’ ) ) ) THEN 0 ELSE 1 END = 1 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 sprinc.[name] WHEN ‘R’ THEN NULL — Database Role WHEN ‘G’ THEN NULL — Windows Group ELSE NULL END, [UserType] = CASE memberprinc.[type] WHEN ‘S’ THEN ‘SQL User’ WHEN ‘U’ THEN ‘Windows User’ WHEN ‘R’ THEN NULL — Database Role WHEN ‘G’ THEN NULL — Windows Group END, [PrincipalUserName] = COALESCE( CASE memberprinc.[type] WHEN ‘S’ THEN memberprinc.[name] WHEN ‘U’ THEN sprinc.[name] WHEN ‘R’ THEN NULL — Database Role WHEN ‘G’ THEN NULL — Windows Group ELSE NULL END, memberprinc.[name] ), [PrincipalType] = CASE memberprinc.[type] WHEN ‘S’ THEN ‘SQL User’ WHEN ‘U’ THEN ‘Windows User’ WHEN ‘R’ THEN ‘Database Role’ WHEN ‘G’ THEN ‘Windows Group’ END, [DatabaseUserName] = memberprinc.[name], [Role] = roleprinc.[name], [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = obj.type_desc,—perm.[class_desc], [ObjectSchema] = OBJECT_SCHEMA_NAME(perm.major_id), [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.server_principals sprinc on memberprinc.[sid] = sprinc.[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 CASE WHEN @HideDatabaseDiagrams = 1 AND memberprinc.[name] = ‘guest’ AND ( ( obj.type_desc = ‘SQL_SCALAR_FUNCTION’ AND OBJECT_NAME(perm.major_id) = ‘fn_diagramobjects’ ) OR ( obj.type_desc = ‘SQL_STORED_PROCEDURE’ AND OBJECT_NAME(perm.major_id) IN ( N’sp_alterdiagram’, N’sp_creatediagram’, N’sp_dropdiagram’, N’sp_helpdiagramdefinition’, N’sp_helpdiagrams’, N’sp_renamediagram’ ) ) ) THEN 0 ELSE 1 END = 1 UNION —List all access provisioned to the public role, which everyone gets by default SELECT [UserName] = ‘{All Users}’, [UserType] = ‘{All Users}’, [PrincipalUserName] = ‘{All Users}’, [PrincipalType] = ‘{All Users}’, [DatabaseUserName] = ‘{All Users}’, [Role] = roleprinc.[name], [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = obj.type_desc,—perm.[class_desc], [ObjectSchema] = OBJECT_SCHEMA_NAME(perm.major_id), [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 AND CASE WHEN @HideDatabaseDiagrams = 1 AND roleprinc.[name] = ‘public’ AND ( ( obj.type_desc = ‘SQL_SCALAR_FUNCTION’ AND OBJECT_NAME(perm.major_id) = ‘fn_diagramobjects’ ) OR ( obj.type_desc = ‘SQL_STORED_PROCEDURE’ AND OBJECT_NAME(perm.major_id) IN ( N’sp_alterdiagram’, N’sp_creatediagram’, N’sp_dropdiagram’, N’sp_helpdiagramdefinition’, N’sp_helpdiagrams’, N’sp_renamediagram’ ) ) ) THEN 0 ELSE 1 END = 1 ORDER BY dbprinc.[Name], OBJECT_NAME(perm.major_id), col.[name], perm.[permission_name], perm.[state_desc], obj.type_desc—perm.[class_desc] Ответ №9

Большое спасибо за потрясающие сценарии аудита.

Я настоятельно рекомендую для аудита использовать удивительные хранимые процедуры Кеннета Фишера (b | t):

Ответ №10

К сожалению, я не смог прокомментировать сообщение Шона Роуза из-за недостаточной репутации, однако мне пришлось изменить часть сценария с “публичной” ролью, так как он не отображал права доступа SCHEMA из-за (INNER) JOIN против sys. объекты. После того, как это было изменено на LEFT JOIN, мне также пришлось изменить логику предложения WHERE, чтобы исключить системные объекты. Мой исправленный запрос на публичные пермиты ниже.

—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] Ответ №11

Я попробовал почти все из этого, но я быстро заметил, что некоторые из них отсутствуют, особенно пользователи sysadmin. Наличие такой дыры не будет хорошо выглядеть в нашей предстоящей ревизии, так что это то, что я придумал

USE master GO SELECT DISTINCT p.name AS [loginname] , —p.type, p.type_desc , p.is_disabled, s.sysadmin, sp.permission_name FROM sys.server_principals p INNER JOIN sys.syslogins s ON p.sid = s.sid INNER JOIN sys.server_permissions sp ON p.principal_id = sp.grantee_principal_id WHERE p.type_desc IN (‘SQL_LOGIN’, ‘WINDOWS_LOGIN’, ‘WINDOWS_GROUP’) — Logins that are not process logins AND p.name NOT LIKE ‘##%’ ORDER BY p.name GO

Оцените статью
Добавить комментарий

© 2025 TechArks.Ru