Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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 @ 8:27 AM
Points: 107, Visits: 633
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


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 11:59 AM
Points: 594, Visits: 933
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 @ 8:27 AM
Points: 107, Visits: 633
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


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 11:59 AM
Points: 594, Visits: 933
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 @ 8:27 AM
Points: 107, Visits: 633
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 @ 1:13 PM
Points: 40,428, Visits: 36,879
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 2008, MVP
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