SET NOCOUNT ON
DECLARE @DBName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
CREATE TABLE #dbTable
(DBName varchar(50) null ,
UserName varchar(100) null,
RoleName varchar(100) null)
DECLARE dbCursor CURSOR FOR
SELECT name AS DbName
FROM sys.databases
WHERE name not in( 'master', 'msdb' , 'tempdb', 'model' )
AND state=0
OPEN dbCursor
FETCH NEXT FROM dbCursor INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'USE ' + @DBName
--Print @DBName
EXEC (@sql)
INSERT INTO #dbTable (DBName, [UserName], RoleName )
SELECT @DBName as DBNames, u.[name] AS [UserName], r.[name] AS RoleName
FROM sys.database_principals u
JOIN sys.database_role_members drm ON u.principal_id = drm.member_principal_id
JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
AND u.[name] NOT IN('dbo')
ORDER BY u.[name]
FETCH NEXT FROM dbCursor INTO @DBName
END
CLOSE dbCursor
DEALLOCATE dbCursor
SELECT * FROM #dbTable
--Drop Table #dbTable
GO