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 Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 24, 2014 1:27 AM
Points: 99, Visits: 588
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: Thursday, October 23, 2014 11:21 AM
Points: 593, Visits: 929
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 Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 24, 2014 1:27 AM
Points: 99, Visits: 588
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: Thursday, October 23, 2014 11:21 AM
Points: 593, Visits: 929
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 Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 24, 2014 1:27 AM
Points: 99, Visits: 588
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 @ 6:05 AM
Points: 40,258, Visits: 36,681
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