Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Who is in the DBO group for all dbs. Expand / Collapse
Author
Message
Posted Tuesday, December 3, 2013 8:56 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 4:39 AM
Points: 165, Visits: 991
Hi

I need a script as part of a security audit. Any pointers would help.
Even a table name where this stored will do.
Post #1519267
Posted Tuesday, December 3, 2013 9:08 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, February 17, 2016 11:43 AM
Points: 604, Visits: 959
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
Post #1519270
Posted Tuesday, December 3, 2013 10:10 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 4:39 AM
Points: 165, Visits: 991
I need to know who is in the DBO role for each database on my server.
Via a script.
Post #1519312
Posted Tuesday, December 3, 2013 10:37 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, February 17, 2016 11:43 AM
Points: 604, Visits: 959
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
Post #1519320
Posted Wednesday, December 4, 2013 3:02 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 4:39 AM
Points: 165, Visits: 991
Excellent. Nice one. Never used Sysmembers. Every day is a school day.
Post #1519538
Posted Wednesday, December 4, 2013 3:04 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:23 AM
Points: 44,468, Visits: 42,071
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

Post #1519539
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse