This cursor inserts all of the roles and members from each database into your admin database.
USE [SQL_DBA_Sandbox]
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Roles]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Roles](
[DBName] [varchar](max) NULL,
[Role] [varchar](max) NULL,
[Member] [varchar](max) NULL
) ON [PRIMARY]
END
ELSE
DELETE FROM SQL_DBA_Sandbox.dbo.Roles;
GO
DECLARE @DB_NAME sysname;
SET @DB_NAME = NULL;
DECLARE @SQL VARCHAR(2000);
DECLARE Cursor_DB CURSOR FAST_FORWARD READ_ONLY FOR
SELECT name FROM sys.databases
WHERE database_id > 4
OPEN Cursor_DB
FETCH NEXT FROM Cursor_DB INTO @DB_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL =
'INSERT INTO SQL_DBA_Sandbox.dbo.Roles
( DBNAME, ROLE, MEMBER )
SELECT ''' + @DB_NAME + ''',
r.name AS ''role'',
mem.name AS ''member''
FROM [' + @DB_Name + '].sys.database_role_members rm
INNER JOIN [' + @DB_Name + '].sys.database_principals r ON r.principal_id = rm.role_principal_id
INNER JOIN [' + @DB_Name + '].sys.database_principals mem ON mem.principal_id = rm.member_principal_id';
PRINT @SQL;
EXEC (@SQL);
FETCH NEXT FROM Cursor_DB INTO @DB_NAME
END
CLOSE Cursor_DB
DEALLOCATE Cursor_DB
SELECT DISTINCT DBName,[Role] FROM SQL_DBA_Sandbox.dbo.Roles
WHERE Role = 'analyst'
SELECT * FROM SQL_DBA_Sandbox.dbo.roles
WHERE Member = 'my_user'