如何查看 SQL 数据库的所有授权?

问题描述

我正在使用 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 需要检查成员身份，因为它提供隐式的插入、更新和删除权限，这意味着您不会看到它显示在权限 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 数据库的所有授权?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！