SET NOCOUNT ON

DECLARE @DBName VARCHAR(255)

DECLARE @sqlStm NVARCHAR(500)

CREATE TABLE #dbTable

(

ServerName varchar(200) ,

DBNamevarchar(255) ,

UserNamevarchar(100) ,

RoleNamevarchar(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