System query to determine full (including inherited from AD ROLES) view of permissions in a database?

Question

In (SQL 2005 or 2008), is it possible to run a custom query against system tables, or to run some sp_help* proc, that would display all permissions for a given user in the database, including those inherited from AD Group memberships?

Update #1

I am wanting to see all users that have permissions by virtue of membership in AD Groups (that have been granted permissions in the database). So is [UserA] is a member of AD group [SuperUsers], and [SuperUsers] belongs to the SQL Role db_datareader, then I am hoping to see [UserA] with db_datareader permissions. ([UserA] in this case has not explicitly been directly granted any permissions to the database, all permissions are inherited from group [SuperUsers]

To test the examples posted so far, I added a new user to my database, set its login name to a known AD group, and granted db_datareader to the AD Group. What I am wanting to see is, all AD users within that AD group will be seen within this query.

I am not seeing this output using any of the posted examples. Are they providing something different, or do they actually do what I want, but my test configuration is wrong?

Perhaps this is literally not possible, as it would require SQL Server to be able to enumerate Active Directory Groups, which perhaps it simply cannot do?

Update #2

SQL Server is not natively aware of AD Users within AD Groups, so it is not possible to accomplish this.

Note: another question related to this (but dealing with SQL Server users and roles, not AD Users and groups) can be found here:

SQL Server query to find all permissions/access for all users in a database

Answer 1

This might be a useful start:

-- 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

This is not possible.

As @JeremyPridemore notes: SQL Server can not access AD information without doing a SQLCLR call to an AD service. (or some other form of call to AD)

Answer 3

The system stored procedure xp_logininfo will deliver members of an AD Group when called this way:

EXEC xp_logininfo @acctname= 'domain\group', @option = 'members';

Note: this requires the AD Group be setup as a SQL Login first otherwise will return 0 rows regardless of actual membership in AD

Reference link: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-logininfo-transact-sql?view=sql-server-ver15