查找对数据库中表的用户帐户访问权限

Question

我正在尝试编写一个查询来告诉我某个用户可以访问数据库中的哪些表。这是域用户而不仅仅是 SQL 用户。

提前致谢。

Answer 1

假设您只对用户/登录而不是角色等感兴趣，您可以按如下方式获取信息：

WITH    RootPermissions
          AS ( SELECT   USER_NAME(p.grantee_principal_id) AS UserName ,
                        dp.principal_id ,
                        dp.type_desc AS UserType ,
                        OBJECT_NAME(p.major_id) AS ObjectName ,
                        p.class_desc AS ObjectType ,
                        p.permission_name AS Permission,
                        p.state_desc AS PermissionState
               FROM     sys.database_permissions AS p
                        INNER JOIN sys.database_principals AS dp ON p.grantee_principal_id = dp.principal_id
             ),
        UnionResults ( UserName, UserType, ObjectName, ObjectType, Permission, PermissionState, role_name )
          AS ( SELECT   UserName ,
                        UserType ,
                        ObjectName ,
                        ObjectType ,
                        Permission ,
                        PermissionState ,
                        CAST(NULL AS SYSNAME) AS role_name
               FROM     RootPermissions AS p
               WHERE    ( UserType <> 'DATABASE_ROLE' )
               UNION
               SELECT   rm.member_principal_name ,
                        rm.principal_type_desc ,
                        p.ObjectType ,
                        p.ObjectName ,
                        p.Permission ,
                        p.PermissionState ,
                        rm.role_name
               FROM     RootPermissions AS p
                        RIGHT OUTER JOIN ( SELECT   rm.role_principal_id ,
                                                    dp.type_desc AS principal_type_desc ,
                                                    rm.member_principal_id ,
                                                    USER_NAME(rm.member_principal_id) AS member_principal_name ,
                                                    USER_NAME(rm.role_principal_id) AS role_name
                                           FROM     sys.database_role_members AS rm
                                                    INNER JOIN sys.database_principals AS dp ON rm.member_principal_id = dp.principal_id
                                         ) AS rm ON rm.role_principal_id = p.principal_id
             )
    SELECT  ObjectName ,
            UserName ,
            ObjectType ,
            UserType ,
            Permission ,
            PermissionState ,
            role_name
    FROM    UnionResults
    WHERE   ( ObjectName IS NOT NULL )
    ORDER BY ObjectName ,
            UserName