主页 / dba / 问题 / 53315
Accepted
BJury
Asked: 2013-11-15 03:51:29 +0800 CST

查找对象上的给定用户正在使用哪些权限

  • 772

有什么方法可以让我看到正在使用哪些权限来确定用户对给定对象的访问权限？我能够看到已将哪些权限授予数据库中的各种角色和用户，但是这些报告与实际发生的情况不一致时遇到了问题。

例如，下面的 SQL 将返回我对给定对象的有效权限：

SELECT * FROM fn_my_permissions('dbo.SomeTable', 'OBJECT') 
ORDER BY subentity_name, permission_name ;

返回：

entity_name         subentity_name permission_name
------------------- -------------- ---------------
dbo.SomeTable                      INSERT
dbo.SomeTable                      SELECT
dbo.SomeTable                      UPDATE

非常有用的是以下内容，它解释了每个行项目的来源：

entity_name         subentity_name permission_name reason
------------------- -------------- --------------- ----------------
dbo.SomeTable                      INSERT          dbowner
dbo.SomeTable                      SELECT          role: datareader
dbo.SomeTable                      UPDATE          role: sysadmin

请注意，网站上有一个类似的问题，但是接受的答案是返回设置的权限的查询，而不是所述权限的来源。

编辑：询问的原因是这里看到并解释了以前权限的“阴影” 。但是，如果我可以访问类似上面的内容，它将表明 SQL Server 认为我是给定角色的成员，从而为我指明了正确的方向......

sql-server sql-server-2008

2 个回答

  1. Best Answer

    看看sys.user_token（http://technet.microsoft.com/en-us/library/ms188421.aspx）和sys.login_token（http://technet.microsoft.com/en-us/library/ms186740.aspx）。在确定当前用户的权限时，它们会告诉您 SQL Server 涉及哪些角色/登录名/用户。

    然后您可以使用sys.database_permissions( http://msdn.microsoft.com/en-us/library/ms188367.aspx ) 和sys.server_permissions( http://msdn.microsoft.com/en-us/library/ms186260.aspx ) 获取与每个令牌关联的权限。（他们甚至会告诉你是谁授予了许可。）

    最后，sys.database_principals（http://msdn.microsoft.com/en-us/library/ms187328.aspx）和sys.server_principals（http://msdn.microsoft.com/en-us/library/ms188786.aspx）可以用来翻译将 principal_id 转换为名称。

    我尝试结合上面的系统视图。它可能并不完美，但它应该让你开始：

    SELECT  
        T.name token_name,
        T.type token_type,
        T.usage,
        T.is_login_token,
        UDP.name user_name,
        UDP.type_desc user_type_desc,
        UDP.default_schema_name,
        UDP.create_date,
        UDP.is_fixed_role,
        --UDP.authentication_type_desc,
        SP.name login_name,
        SP.type_desc login_type,
        SP.create_date,
        --SP.is_fixed_role,
        DP.class_desc,
        DP.permission_name,
        DP.state_desc,
        QUOTENAME(OBJECT_SCHEMA_NAME(O.object_id))+'.'+QUOTENAME(O.name) object_name,
        O.type_desc object_type_desc,
        C.name column_name
  FROM (SELECT 0 is_login_token,* FROM sys.user_token 
        UNION ALL
        SELECT 1 is_login_token,* FROM sys.login_token
       )AS T
  LEFT JOIN sys.database_principals AS UDP
    ON T.principal_id = UDP.principal_id
   AND T.is_login_token = 0
  LEFT JOIN sys.server_principals AS SP
    ON (UDP.sid = SP.sid AND T.is_login_token = 0)
    OR (T.principal_id = SP.principal_id AND T.is_login_token = 1)
  LEFT JOIN sys.database_permissions AS DP
    ON (DP.grantee_principal_id = UDP.principal_id
        AND T.is_login_token = 0)
    OR (DP.grantee_principal_id = SP.principal_id
        AND T.is_login_token = 1)
  LEFT JOIN sys.all_objects AS O
    ON DP.major_id = O.object_id
  LEFT JOIN sys.all_columns AS C
    ON DP.major_id = C.object_id
   AND DP.minor_id = C.column_id;
    • 4

  2. 不确定这应该是答案还是评论。但是我以前在这里使用过脚本来解决这类问题 -

    http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions--_2800_2_2900_.aspx

    Jamie 使用的最后一个查询显示权限，如果是通过角色，则指定角色。如果不是，则没有指定角色。这是为 SQL Server 2005 编写的，我在 SQL Server 2008 上使用它没有问题。

    • 0

相关问题