如何查看 SQL 数据库的所有授权？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/497317/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I view all grants for an SQL Database?
提问by Joel Briggs
I am using SQL Server 2005, I want to find out what all the grants are on a specific database for all tables. It would also help to find out all tables where the delete grant has been given for a specific user.
我正在使用 SQL Server 2005，我想找出所有表的特定数据库上的所有授权。它还有助于找出已为特定用户授予删除权限的所有表。
Note: this may be similar to this question, but I could not get the selected answer's solution working (if someone could provide a better example of how to use that, it would help as well)
注意：这可能与此问题类似，但我无法使所选答案的解决方案起作用（如果有人可以提供一个更好的示例来说明如何使用它，它也会有所帮助）
回答by K. Brian Kelley
The given solution does not cover where the permission is granted against the schema or the database itself, which do grant permissions against the tables as well. This will give you those situations, too. You can use a WHERE clause against permission_name to restrict to just DELETE.
给定的解决方案不包括针对架构或数据库本身授予权限的位置，它们也授予针对表的权限。这也会给你这些情况。您可以对permission_name 使用WHERE 子句来限制为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
Also, db_datawriter would need to be checked for membership because it gives implicit INSERT, UPDATE, and DELETE rights, meaning you won't see it show up in the permission DMVs or their derivatives.
此外，需要检查 db_datawriter 的成员资格，因为它提供了隐式的 INSERT、UPDATE 和 DELETE 权限，这意味着您不会看到它出现在许可 DMV 或其衍生产品中。
回答by jgoerke
I liked the answer from K. Brian Kelly but I wanted a little more information (like the schema) as well as generating the corresponding GRANT and REVOKE statements so I could apply them in different environments (e.g. dev/test/prod).
我喜欢 K. Brian Kelly 的回答，但我想要更多信息（如模式）以及生成相应的 GRANT 和 REVOKE 语句，以便我可以将它们应用于不同的环境（例如 dev/test/prod）。
note you can easily exclude system objects, see commented where clause
请注意，您可以轻松排除系统对象，请参阅注释的 where 子句
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
回答by splattne
To list all the permissions that can be controlled you can use the function fn_my_permission. This query lists all permissions on server:
要列出可以控制的所有权限，您可以使用该功能fn_my_permission。此查询列出了服务器上的所有权限：
select * from fn_my_permissions(NULL, NULL)
You have to login using an account that has sysadmin role.
您必须使用具有 sysadmin 角色的帐户登录。
You can refine the function calls using following parameters.
您可以使用以下参数优化函数调用。
For all permissions on database:
对于数据库的所有权限：
select * from fn_my_permissions(NULL, 'database')
For all permissions on the dbo schema:
对于 dbo架构的所有权限：
select * from fn_my_permissions('dbo', 'schema')
For all permissions on a table:
对于表的所有权限：
select * from fn_my_permissions('dbo.test', 'object')
回答by Adam
To view all grants on a specific database use this:
要查看特定数据库上的所有授权，请使用：
Select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES
To just view delete grants on a specific database use this:
要查看特定数据库上的删除授权，请使用以下命令：
Select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE PRIVILEGE_TYPE = 'DELETE'
回答by audiphilth
To see the grants on an entire DB, select the DB in question, open a new query window, enter - sp_helprotect, execute query
要查看整个数据库的授权，请选择有问题的数据库，打开一个新的查询窗口，输入 - sp_helprotect，执行查询
