CODEWITHSUNDEEP
sqlt-sql
thaibythai
thaibythai

Reputation: 41

List db_owner privileges on each database for a user

I have a user John.Smith and I want to know what database he have db_owner too and well as what database he doesn't have db_owner. Is there a script I can run? We have a lot of database and when I look at the usermap I feel like I might be over looking something.

Upvotes: 4

Views: 3135

Answers (1)

JodyT
JodyT

Reputation: 4412

You could use something like:

CREATE TABLE ##DBOwners
(
    DBName SYSNAME,
    Username SYSNAME,
    Rolename NVARCHAR(50),
    IsOwner BIT
);

EXEC sp_MSforeachdb
    '
        USE [?];
        INSERT INTO ##DBOwners
            SELECT
                DB_NAME(),
                dp.name,
                dp2.name,
                CASE 
                    WHEN dp2.name = ''db_owner'' THEN 1
                    ELSE 0
                END AS ''db_owner''
            FROM
                sys.database_principals AS dp
            LEFT JOIN
                sys.database_role_members AS rm
                    ON
                        rm.member_principal_id = dp.principal_id
            LEFT JOIN
                sys.database_principals AS dp2
                    ON
                        rm.role_principal_id = dp2.principal_id
            WHERE
                dp.type <> ''R''
    '

SELECT * FROM ##DBOwners WHERE Username = 'John.Smith'
DROP TABLE ##DBOwners;

It gathers information about all databases by using the stored procedure sp_MSforeachdb and saving the results in a global temp table.

Upvotes: 2

Related Questions