Who is in the DBO group for all dbs.

Question

Who is in the DBO group for all dbs.

Super Cat

SSCertifiable

Points: 7332
More actions
December 3, 2013 at 8:56 am

#395932

Hi
I need a script as part of a security audit. Any pointers would help.
Even a table name where this stored will do.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Keith Tate SSCrazy Eights Points: 9888 More actions · Answer 1

I'm not sure what you are asking with this question. If you want a table to list every principal on the server you can use sys.server_principals

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Super Cat SSCertifiable Points: 7332 More actions · Answer 2

I need to know who is in the DBO role for each database on my server.

Via a script.

Keith Tate SSCrazy Eights Points: 9888 More actions · Answer 3

Here is the query:

SELECT

USER_NAME(memberuid) as Member

,USER_NAME(groupuid) as [Group]

FROM

sys.sysmembers

WHERE

USER_NAME(groupuid) = 'db_owner'

and you can use sp_MSforeachdb to run it for all databases on your server.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Super Cat SSCertifiable Points: 7332 More actions · Answer 4

Excellent. Nice one. Never used Sysmembers. Every day is a school day.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

sys.sysmembers (Transact-SQL)
This SQL Server 2000 system table is included as a view for backward compatibility. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

The 2008 equivalent is sys.database_role_members.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass