SET NOCOUNT ON
DECLARE @DBName VARCHAR(255)
DECLARE @sqlStm NVARCHAR(500)
CREATE TABLE #dbTable
(
ServerName varchar(200) ,
DBName varchar(255) ,
UserName varchar(100) ,
RoleName varchar(100)
)
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 @sqlStm = 'USE ' + @DBName
--Print @DBName
--EXEC (@sqlStm)
--INSERT INTO #dbTable (ServerName , DBName, [UserName], RoleName )
EXECUTE('USE ' + @DBName + '; INSERT INTO #dbTable (ServerName , DBName, [UserName], RoleName ) SELECT @@ServerName , db_name(), u.[name] , r.[name]
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')
FETCH NEXT FROM dbCursor INTO @DBName
END
CLOSE dbCursor
DEALLOCATE dbCursor
SELECT * FROM #dbTable
DROP TABLE #dbTable
GO