系统查询，用于确定数据库中权限的完整(包括从 AD 角色继承的)视图

Question

In （SQL 2005 或 2008），是否可以对系统表运行自定义查询，或运行一些 sp_help* 进程，以显示数据库中给定用户的所有权限，包括从 AD 组成员身份继承的权限？

更新 #1

我想查看通过 AD 组中的成员身份（已在数据库中授予权限）具有权限的所有用户。 所以 [UserA] 是 AD 组 [SuperUsers] 的成员，并且 [SuperUsers] 属于 SQL 角色db_datareader，那么我希望看到具有db_datareader权限的 [UserA]。 （[用户 A] 在本例中未被显式直接授予对数据库的任何权限，所有权限都继承自组 [超级用户]

为了测试到目前为止发布的示例，我向数据库添加了一个新用户，将其登录名设置为已知的 AD 组，并向 AD 组授予db_datareader。我想看到的是，该 AD 组中的所有 AD 用户都将在此查询中看到。

我没有看到使用任何发布的示例的输出。 他们是否提供了不同的东西，或者他们实际上做了我想要的，但我的测试配置是错误的？

也许这实际上是不可能的，因为它需要SQL Server能够枚举Active Directory组，也许它根本无法做到？

更新 #2

SQL Server 本身无法识别 AD 组中的 AD 用户，因此无法完成此操作。

注意：与此相关的另一个问题（但处理的是 SQL Server 用户和角色，而不是 AD 用户和组）可以在此处找到：

用于查找数据库中所有用户的所有权限/访问权限的 SQL Server 查询

Answer 1

这可能是一个有用的开始：

-- Server Pricipals - Who has access to this server?
SELECT 
    'Server Principals'
    , ServerPrincipals.name AS ServerObject
    , ServerPrincipals.type_desc AS ServerObjectType
    , ServerPrincipals.is_disabled AS [Disabled]
FROM sys.server_principals ServerPrincipals
ORDER BY type_desc
-- Server Roles - What server roles do my server logins have?
SELECT 
    'Server Roles'
    , ServerMemberPrincipal.name
    , ServerMemberPrincipal.type_desc
    , ServerRolePrincipal.name
    , ServerRolePrincipal.type_desc
FROM sys.server_role_members ServerRoleMembers
INNER JOIN sys.server_principals ServerMemberPrincipal
    ON ServerRoleMembers.member_principal_id = ServerMemberPrincipal.principal_id
INNER JOIN sys.server_principals ServerRolePrincipal
    ON ServerRoleMembers.role_principal_id = ServerRolePrincipal.principal_id
-- Database Users - Who has access to this database?
SELECT
    'Database Users'
    , UserName = DatabasePrincipal.name
    , UserType = DatabasePrincipal.type_desc
    , LoginName = sp.name
    , LoginType = sp.type_desc
FROM sys.database_principals DatabasePrincipal
INNER JOIN sys.server_principals sp 
    ON DatabasePrincipal.principal_id = sp.principal_id
ORDER BY LoginType, UserName, UserType, LoginName
-- Database Roles - What database roles do the users in this database have?
SELECT
    'Database Roles'
    , DatabaseMemberPrincipal.name
    , DatabaseMemberPrincipal.type_desc
    , DatabaseRolePrincipal.name
    , DatabaseRolePrincipal.type_desc
    , DatabaseRolePrincipal.is_fixed_role
FROM sys.database_role_members DatabaseRoleMembers
INNER JOIN sys.database_principals DatabaseMemberPrincipal
    ON DatabaseRoleMembers.member_principal_id = DatabaseMemberPrincipal.principal_id
INNER JOIN sys.database_principals DatabaseRolePrincipal 
    ON DatabaseRoleMembers.role_principal_id = DatabaseRolePrincipal.principal_id
-- Database Permissions - What can my database users and roles do?  
SELECT
    'Database Permissions'
    , GranteeName = grantee.name
    , [State] = DatabasePermissions.state_desc
    , Granted = DatabasePermissions.permission_name
    , [For] = DatabasePermissions.class_desc
    , [On] = COALESCE(OBJECT_NAME(DatabasePermissions.major_id), XmlSchemaCollection.name, DbName.Name, SymmetricKeys.Name, CertificateName.Name)
    , [By] = grantor.name
FROM sys.database_permissions DatabasePermissions
INNER JOIN sys.database_principals Grantee 
    ON DatabasePermissions.grantee_principal_id = grantee.principal_id
INNER JOIN sys.database_principals Grantor 
    ON DatabasePermissions.grantor_principal_id = grantor.principal_id
LEFT JOIN sys.xml_schema_collections XmlSchemaCollection
    ON XmlSchemaCollection.xml_collection_id = DatabasePermissions.major_id
    AND DatabasePermissions.class_desc = 'XML_SCHEMA_COLLECTION'
OUTER APPLY (SELECT DB_NAME() AS Name WHERE DatabasePermissions.class_desc = 'DATABASE') DbName
LEFT JOIN sys.symmetric_keys SymmetricKeys
    ON SymmetricKeys.symmetric_key_id = DatabasePermissions.major_id
    AND DatabasePermissions.class_desc = 'SYMMETRIC_KEYS'
LEFT JOIN sys.certificates CertificateName
    ON CertificateName.certificate_id = DatabasePermissions.major_id
    AND DatabasePermissions.class_desc = 'CERTIFICATE'
ORDER BY GranteeName, Granted, [For], [On], [State], [By]

Answer 2

这

是不可能的。

正如@JeremyPridemore所指出的：如果不对AD服务进行SQLCLR调用，SQL Server就无法访问AD信息。（或其他形式的 AD 调用）

Answer 3

系统存储过程 xp_logininfo 将以下列方式调用时传递 AD 组的成员：

EXEC xp_logininfo @acctname= 'domaingroup', @option = 'members';

注意：这需要首先将 AD 组设置为 SQL 登录名，否则将返回 0 行，而不管 AD 中的实际成员身份如何

参考链接：https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-logininfo-transact-sql?view=sql-server-ver15