How to find which user has DBO right

_{Wednesday, August 8, 2012 6:40 PM}

I want to know which user has DBO right on each database. I am using SQL 2005 and SQL 2008. Basically I want to have following information:

Any help would be highly appreciated. 

_{Wednesday, August 8, 2012 7:32 PM ✅Answered}

Hi,

You may try this:

exec sp_msForEachDb ' use [?]
select db_name() as [database_name], r.[name] as [role], p.[name] as [member] from 
    sys.database_role_members m
join
    sys.database_principals r on m.role_principal_id = r.principal_id
join
    sys.database_principals p on m.member_principal_id = p.principal_id
where
    r.name = ''db_owner'''

I hope it helps.

Janos

There are 10 type of people. Those who understand binary and those who do not.

My Blog | Hire Me

_{Wednesday, August 8, 2012 7:37 PM | 1 vote}

Run this for every Server and every database of each Server that you care about.

select @@ServerName [Server Name], DB_NAME() [DB Name], u.name [DB Role], u2.name [Member Name]from sys.database_role_members mjoin sys.database_principals u on m.role_principal_id = u.principal_idjoin sys.database_principals u2 on m.member_principal_id = u2.principal_idwhere u.name = 'db_owner'order by [Member Name]

You can try using sp_MSForEachDB or Aaron Bertrand's replacement (http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx).  Or just write your own code to cycle through the databases.

RLF