how can i get a list of all users with db_owner role in any database

• alen teplitsky

  SSC-Dedicated

  Points: 30014

  More actions

  September 30, 2009 at 1:05 pm

  #226691

  this is for a SOX audit

  we need to provide a list of all users on a server with db_owner permissions. is there an easy way to do it?

• Mark Marinovic

  SSCrazy

  Points: 2472

  More actions

  September 30, 2009 at 1:25 pm

  #1060152

  This is what I have used (SQL 90+):

  EXEC sp_MSForEachDB 'SELECT ''?'' AS [Database Name], su1.name AS [Database User Name], su2.name AS [Database Role]

  FROM [?].sys.database_role_members r

  INNER JOIN [?]..sysusers su1 ON su1.[uid] = r.member_principal_id

  INNER JOIN [?]..sysusers su2 ON su2.[uid] = r.role_principal_id

  WHERE su2.name IN(''db_owner'') AND su1.name NOT IN(''dbo'')'

  Note that this is an Admin type discovery script and as such, the IN/NOT IN constructs are there for reusability. This will report any db user in the db_owner role for a given instance. You can modify the code to fit your needs though.

  MJM

• Tim Benninghoff

  SSCrazy

  Points: 2593

  More actions

  September 30, 2009 at 1:52 pm

  #1060165

  In addition to Mark's handy SQL statement, you'll also probably want to report the actual owner of the database, which you can find with:

  SELECT name, suser_sname(owner_sid) FROM sys.databases;