如何查看SQL数据库的所有授予? [英] How can I view all grants for an SQL Database?

问题描述

我正在使用SQL Server 2005，我想找出所有表在特定数据库上的所有授权.找出所有为特定用户授予删除权限的表也将有所帮助.

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.

注意:这可能类似于

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)

推荐答案

给定的解决方案不涉及针对架构或数据库本身授予权限的位置，而对数据库或表本身也授予权限.这也会给您带来这些情况.您可以对permission_name使用WHERE子句以将其限制为仅DELETE.

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.

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或其派生类中看到它.

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.

这篇关于如何查看SQL数据库的所有授予?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！