views:

512

answers:

2

Hi,

I am using SQL 2000 and SQL 2005.

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

I can click on users or database roles in every database to see that. Could this be done in an easier way using TSQL?

Thanks in advance

A: 

It's sloppy and there is probably a better way, but this should get it 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
Tom H.
tHANKS.hOW CAN I RUN IT IN sql 2000?
Manjot
Won't work in SQL Server 2000...
gbn
+2  A: 

For SQL 2000 and still works for SQL 2005 too

SELECT
    USER_NAME(memberuid), USER_NAME(groupuid)
FROM
    sys.sysmembers
WHERE
    USER_NAME(groupuid) IN ('db_owner', 'db_accessadmin')
gbn
Thank you very much. And to run it in all database I did:EXEC SP_MSFOREACHDB 'USE [?] ; SELECT DB_NAME(),USER_NAME(memberuid), USER_NAME(groupuid) FROM sysmembers WHERE USER_NAME(groupuid) IN (''db_owner'', ''db_accessadmin'') and user_name(memberuid) not like ''%dbo%'''
Manjot
sorry, I did that in my local SQL Server but forgot to post it!
gbn