Как просмотреть все гранты для базы данных SQL?

Я использую SQL Server 2005, я хочу узнать, какие все гранты находятся в конкретной базе данных для всех таблиц. Это также поможет узнать все таблицы, в которых грант удаления был предоставлен конкретному пользователю.

Примечание: это может быть похоже на этот вопрос, но я не смог заставить выбранное решение ответа работать (если бы кто-то мог предоставить лучший пример того, как использовать этот, это тоже помогло бы)

Ответ 1

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

SELECT 
    class_desc 
  , CASE WHEN class = 0 THEN DB_NAME()
         WHEN class = 1 THEN OBJECT_NAME(major_id)
         WHEN class = 3 THEN SCHEMA_NAME(major_id) END [Securable]
  , USER_NAME(grantee_principal_id) [User]
  , permission_name
  , state_desc
FROM sys.database_permissions

Кроме того, db_datawriter нужно будет проверить для членства, потому что он дает неявные права INSERT, UPDATE и DELETE, то есть вы не увидите его в разрешении DMV или их производных.

Ответ 2

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

Select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES

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

Select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE PRIVILEGE_TYPE = 'DELETE'

Ответ 3

Мне понравился ответ от К. Брайана Келли, но я хотел получить немного больше информации (например, схему), а также для создания соответствующих операторов GRANT и REVOKE, чтобы я мог применять их в разных средах (например, dev/test/prod).

обратите внимание, что вы можете легко исключить системные объекты, см. комментарий, где предложение

select 
    class_desc 
    ,USER_NAME(grantee_principal_id) as user_or_role
    ,CASE WHEN class = 0 THEN DB_NAME()
          WHEN class = 1 THEN ISNULL(SCHEMA_NAME(o.uid)+'.','')+OBJECT_NAME(major_id)
          WHEN class = 3 THEN SCHEMA_NAME(major_id) END [Securable]
    ,permission_name
    ,state_desc
    ,'revoke ' + permission_name + ' on ' +
        isnull(schema_name(o.uid)+'.','')+OBJECT_NAME(major_id)+ ' from [' +
        USER_NAME(grantee_principal_id) + ']' as 'revokeStatement'
    ,'grant ' + permission_name + ' on ' +
        isnull(schema_name(o.uid)+'.','')+OBJECT_NAME(major_id)+ ' to ' +
        USER_NAME(grantee_principal_id) + ']' as 'grantStatement'
FROM sys.database_permissions dp
LEFT OUTER JOIN sysobjects o
    ON o.id = dp.major_id
-- where major_id >= 1  -- ignore sysobjects

order by 
    class_desc desc
    ,USER_NAME(grantee_principal_id)
    ,CASE WHEN class = 0 THEN DB_NAME()
         WHEN class = 1 THEN isnull(schema_name(o.uid)+'.','')+OBJECT_NAME(major_id)
         WHEN class = 3 THEN SCHEMA_NAME(major_id) end
    ,permission_name

Ответ 4

Чтобы перечислить все разрешения, которые можно контролировать, вы можете использовать функцию fn_my_permission. В этом запросе перечислены все разрешения на сервере:

select * from fn_my_permissions(NULL, NULL)

Вам нужно войти в систему, используя учетную запись с ролью sysadmin.

Вы можете уточнить вызовы функций, используя следующие параметры.

Для всех разрешений в базе данных:

select * from fn_my_permissions(NULL, 'database')

Для всех разрешений схемы dbo :

select * from fn_my_permissions('dbo', 'schema')

Для всех разрешений в таблице :

select * from fn_my_permissions('dbo.test', 'object')

Ответ 5

Чтобы просмотреть гранты для всей БД, выберите интересующую БД, откройте новое окно запроса, введите - sp_helprotect, выполните запрос