Post reply

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

  • 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?

  • 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

  • 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;

Viewing 3 posts - 1 through 2 (of 2 total)

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