CODEWITHSUNDEEP
sql-serversql-server-2005t-sqlsql-server-2000
Manjot
Manjot

Reputation: 11516

TSQL to know database role members

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

Upvotes: 7

Views: 28027

Answers (2)

Tom H
Tom H

Reputation: 47454

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

The SQL 2000 version should 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

Upvotes: 3

gbn
gbn

Reputation: 432230

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')

Upvotes: 20

Related Questions