I was able to combine suggestions here with an old script I had lying around to come up with the following, which will show all the groups a user is in, along with the database roles they are mapped to. Note that this is not thoroughly tested, but it gave the results I needed.
-- SHOW PERMISSIONS FOR A USER BASED ON MEMBERSHIP IN AD GROUPS ONLY
DECLARE @LoginName varchar(50)
SET @LoginName = 'DOMAIN\USERNAME'
CREATE TABLE #tmpResults
(
[account name] sysname,
[type] char(8),
privilege char(9),
[mapped login name] sysname,
[permission path] sysname NULL
)
INSERT INTO #tmpResults
exec xp_logininfo @LoginName, 'all'
-------------------
DECLARE @DB_USers TABLE
(DBName sysname, UserName sysname, LoginType sysname, AssociatedRole varchar(max),create_date datetime,modify_date datetime)
INSERT @DB_USers
EXEC sp_MSforeachdb
'
use [?]
SELECT ''?'' AS DB_Name,
case prin.name when ''dbo'' then prin.name + '' (''+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') + '')'' else prin.name end AS UserName,
prin.type_desc AS LoginType,
isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,create_date,modify_date
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%'''
SELECT dbname,username, [mapped login name],logintype ,create_date ,modify_date ,
STUFF(
(
SELECT ',' + CONVERT(VARCHAR(500),associatedrole)
FROM @DB_USers user2
WHERE user1.DBName=user2.DBName AND user1.UserName=user2.UserName
FOR XML PATH('')
)
,1,1,'') AS Permissions_user
FROM @DB_USers user1 JOIN #tmpResults ON UserName = [Permission Path]
GROUP BY [mapped login name], dbname,username ,logintype ,create_date ,modify_date
ORDER BY DBName,username
DROP TABLE #tmpResults
xp_logininfo
(docs.microsoft.com/en-us/sql/relational-databases/…) to enumerate AD groups that provide a user with permissions to connect to SQL Server. This will not detail database permissions, though. – Dan Guzman May 18 '17 at 14:11