Below you will find the code to return the members of each database role for all the databases on the server. Simply select the text and copy to the Clipboard.
–Code Source: https://www.sqlservercentral.com/forums/topic/list-all-logins-databases-role-mapping
DECLARE @name sysname,
@sql1 nvarchar(4000),
@maxlen1 smallint,
@maxlen2 smallint,
@maxlen3 smallint
CREATE TABLE #tmpTable
(
DBName sysname NOT NULL ,
UserName sysname NOT NULL,
RoleName sysname NOT NULL
)
DECLARE c1 CURSOR for
SELECT name FROM master.sys.databases where state_desc <> ‘Offline’
OPEN c1
FETCH c1 INTO @name
WHILE @@FETCH_STATUS >= 0
BEGIN
SELECT @sql1 =
‘INSERT INTO #tmpTable
SELECT N”’+ @name + ”’, a.name, c.name
FROM [‘ + @name + ‘].sys.database_principals a
JOIN [‘ + @name + ‘].sys.database_role_members b ON b.member_principal_id = a.principal_id
JOIN [‘ + @name + ‘].sys.database_principals c ON c.principal_id = b.role_principal_id
WHERE a.name != ”dbo”’
EXECUTE (@sql1)
FETCH c1 INTO @name
END
CLOSE c1
DEALLOCATE c1
SELECT @maxlen1 = (MAX(LEN(COALESCE(DBName, ‘NULL’))) + 2)
FROM #tmpTable
SELECT @maxlen2 = (MAX(LEN(COALESCE(UserName, ‘NULL’))) + 2)
FROM #tmpTable
SELECT @maxlen3 = (MAX(LEN(COALESCE(RoleName, ‘NULL’))) + 2)
FROM #tmpTable
SET @sql1 = ‘SELECT ”Database Role Members”, LEFT(DBName, ‘ + LTRIM(STR(@maxlen1)) + ‘) AS ”DB Name”, ‘
SET @sql1 = @sql1 + ‘LEFT(UserName, ‘ + LTRIM(STR(@maxlen2)) + ‘) AS ”User Name”, ‘
SET @sql1 = @sql1 + ‘LEFT(RoleName, ‘ + LTRIM(STR(@maxlen3)) + ‘) AS ”Role Name” ‘
SET @sql1 = @sql1 + ‘FROM #tmpTable ‘
SET @sql1 = @sql1 + ‘ORDER BY DBName, UserName’
EXEC(@sql1)
DROP TABLE #tmpTable