Geek Answers Handbook

TSQL know the role members in the database

I am using SQL 2000 and SQL 2005.

I want to know which logins have db_owner or db_accessadmin permissions for which databases.

I can click on users or database roles in each database to see this. Could this be made easier with TSQL?

Thanks in advance

+7
sql-server tsql sql-server-2005 sql-server-2000
source share
2 answers

For SQL 2000 and still works for SQL 2005

SELECT USER_NAME(memberuid), USER_NAME(groupuid) FROM sys.sysmembers WHERE USER_NAME(groupuid) IN ('db_owner', 'db_accessadmin')
+18
source share

This is messy and probably the best way, but it needs to be done if this is a one-time thing:

 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 WHERE USER_NAME(role_principal_id) IN (''db_owner'', ''db_accessadmin'')' EXEC(@sql) FETCH NEXT FROM db_cursor INTO @db_name END CLOSE db_cursor DEALLOCATE db_cursor

SQL 2000 version must be:

 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 WHERE USER_NAME(groupuid) IN (''db_owner'', ''db_accessadmin'')' EXEC(@sql) FETCH NEXT FROM db_cursor INTO @db_name END CLOSE db_cursor DEALLOCATE db_cursor
+3
source share

More articles:


All Articles