Below you will find the code that will return all the users for each database on the server. Simply select the text and copy to the Clipboard.
–Code if FROM: https://stackoverflow.com/questions/37114898/how-can-i-get-the-list-of-all-database-name-with-their-username-and-roles-in-t-s/37116656#37116656
DECLARE @DB_USers TABLE
( DBName sysname, UserName sysname null, LoginType sysname, AssociatedRole varchar(max))
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
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
CONVERT(VARCHAR(50),DBName) AS ‘DBName’
, CONVERT(VARCHAR(50),UserName) AS ‘User Name’
, CONVERT(VARCHAR(50),LoginType) AS ‘Login Type’
,
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
GROUP BY DBName,UserName ,LoginType
ORDER BY DBName,UserName