Database, Users and their DB Roles (SQL Server 2000 & 2005)

SQL SERVER 2005

DECLARE @db_name SYSNAME, @sql VARCHAR(1000)

DECLARE db_cursor CURSOR FOR

SELECT Name FROM sys.databases

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @db_name

WHILE (@@FETCH_STATUS= 0)

BEGIN

SET @sql =‘SELECT ”’+ @db_name + ”’ AS [Database],

USER_NAME(role_principal_id) AS [Role], USER_NAME(member_principal_id) AS [User]

FROM ‘+ @db_name +‘.sys.database_role_members’

EXEC(@sql)

 FETCH NEXT FROM db_cursor INTO @db_name

END

CLOSE db_cursor

DEALLOCATE db_cursor

SQL SERVER 2000

DECLARE @db_name SYSNAME, @sql VARCHAR(1000)

 DECLARE db_cursor CURSOR FOR

SELECT Name FROM master..sysdatabases

 OPEN db_cursor FETCH NEXT

FROM db_cursor INTO @db_name

WHILE (@@FETCH_STATUS= 0)

 BEGIN

SET @sql =‘SELECT ”’+ @db_name + ”’ AS [Database],

USER_NAME(memberuid) AS [Role], USER_NAME(groupuid) AS [User] 

FROM sysmembers’

EXEC (@sql)

FETCH NEXT FROM db_cursor INTO @db_name

END

CLOSE db_cursor

DEALLOCATE db_cursor;

–====================================

MvM