Finding users with particular roles

  • Hello

    I need to find all users that have DB_Owner or DB_SecurityAdmin on every database. I've got this script that will tell me on a per database level, but running it against each DB isn't particularly efficient!

    select members.name AS UserName, RTRIM(LTRIM(roles.name)) AS RoleName

    from sys.database_principals members

    inner join sys.database_role_members drm

    on members.principal_id = drm.member_principal_id

    inner join sys.database_principals roles

    on drm.role_principal_id = roles.principal_id

    WHERE members.name <> 'dbo' and Roles.Name = 'db_owner' or Roles.name ='db_securityadmin'

    ORDER BY members.name

    I have looked at a few scripts but can't find exactly what I'm looking for.

    Does anyone have anything that will do this?

    Thank you

  • What about cases where a User-defined Database Role is a member of db_owner?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Can use sp_MSForEachDB, like below, to get results from each database on an instance.

    EXEC sp_MSForEachDB 'select members.name AS UserName, RTRIM(LTRIM(roles.name)) AS RoleName

    from sys.database_principals members

    inner join sys.database_role_members drm

    on members.principal_id = drm.member_principal_id

    inner join sys.database_principals roles

    on drm.role_principal_id = roles.principal_id

    WHERE members.name <> ''dbo'' and Roles.Name = ''db_owner'' or Roles.name =''db_securityadmin''

    ORDER BY members.name'

  • UconnDBA (5/22/2013)


    Can use sp_MSForEachDB, like below, to get results from each database on an instance.

    EXEC sp_MSForEachDB 'select members.name AS UserName, RTRIM(LTRIM(roles.name)) AS RoleName

    from sys.database_principals members

    inner join sys.database_role_members drm

    on members.principal_id = drm.member_principal_id

    inner join sys.database_principals roles

    on drm.role_principal_id = roles.principal_id

    WHERE members.name <> ''dbo'' and Roles.Name = ''db_owner'' or Roles.name =''db_securityadmin''

    ORDER BY members.name'

    If you are in the habit of using sp_MSforeachdb you should read this article:

    A more reliable and more flexible sp_MSforeachdb by Aaron Bertrand

    The proc is undocumented and while it may not change anytime soon, it has some serious shortcomings that you should know about before choosing it.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • I have found a script that seems to work ok against a 2008 server, but not against a 2005 server.

    Unfortunately it has sp_MSForEachDB in it, something that may well be the cause of the problem.

    I'll see if I can do it another way...

  • All sp_MSForEachDB does is implement a cursor for you dynamically and run your code against each DB. Check the article I posted. The proc provided does the same thing, except it has fixed some bugs that exist within the Microsoft version.

    The alternative is to implement your own cursor declaratively and using dynamic SQL, pushing the results of your query into a temporary table so you can access results from all databases when the cursor has completed.

    You also never answered my question about nested groups. Does your query to find member users of a specific group handle that?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • OPC: Thank you for the link to the sp_foreachdb replacement. I've encountered problems with the MS version myself and found Aaron's approach very insightful. Thanks again.

  • No problem Ed. Happy to share. I am confident there are millions of calls made to the MS proc every day but it has some issues that should give pause. IIRC Maintenance Plans actually use the proc to iterate over databases for certain task which is a little concerning.

    @robert

    Does your query to find member users of a specific group handle that?

    I'll save you the suspense, your query does not handle nested groups. I alluded to it before, but in order to achieve full resolution you can use a recursive CTE or some type of manual looping mechanism.

    Here is a query that includes a recursive CTE for you from my cache of scripts that works for one database. You could loop over your list of databases and using dynamic sql insert the results of the query below into a temp table to gather the information you require:

    WITH CTE_Roles(role_id, role_name, major_principal_type, member_id, member_name, minor_principal_type)

    AS (

    SELECT dprole.principal_id AS role_id,

    dprole.name AS role_name,

    dprole.type_desc AS major_principal_type,

    dpmembers.principal_id AS member_id,

    dpmembers.name AS member_name,

    dpmembers.type_desc AS minor_principal_type

    FROM sys.database_principals dprole

    JOIN sys.database_role_members dprolemembers ON dprole.principal_id = dprolemembers.role_principal_id

    JOIN sys.database_principals dpmembers ON dprolemembers.member_principal_id = dpmembers.principal_id

    WHERE dprole.name IN ('db_owner', 'db_securityadmin')

    UNION ALL

    SELECT CR.member_id AS role_id,

    CR.member_name AS role_name,

    CR.major_principal_type,

    dpmembers.principal_id AS member_id,

    dpmembers.name AS member_name,

    dpmembers.type_desc AS minor_principal_type

    FROM CTE_Roles CR

    JOIN sys.database_role_members drm ON CR.member_id = drm.role_principal_id

    JOIN sys.database_principals dpmembers ON drm.member_principal_id = dpmembers.principal_id

    )

    SELECT role_id,

    role_name,

    major_principal_type,

    member_id,

    member_name,

    minor_principal_type

    FROM CTE_Roles

    WHERE member_name != 'dbo';

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • opc.three (5/24/2013)


    No problem Ed. Happy to share. I am confident there are millions of calls made to the MS proc every day but it has some issues that should give pause. IIRC Maintenance Plans actually use the proc to iterate over databases for certain task which is a little concerning.

    @robert

    Does your query to find member users of a specific group handle that?

    I'll save you the suspense, your query does not handle nested groups. I alluded to it before, but in order to achieve full resolution you can use a recursive CTE or some type of manual looping mechanism.

    Here is a query that includes a recursive CTE for you from my cache of scripts that works for one database. You could loop over your list of databases and using dynamic sql insert the results of the query below into a temp table to gather the information you require:

    WITH CTE_Roles(role_id, role_name, major_principal_type, member_id, member_name, minor_principal_type)

    AS (

    SELECT dprole.principal_id AS role_id,

    dprole.name AS role_name,

    dprole.type_desc AS major_principal_type,

    dpmembers.principal_id AS member_id,

    dpmembers.name AS member_name,

    dpmembers.type_desc AS minor_principal_type

    FROM sys.database_principals dprole

    JOIN sys.database_role_members dprolemembers ON dprole.principal_id = dprolemembers.role_principal_id

    JOIN sys.database_principals dpmembers ON dprolemembers.member_principal_id = dpmembers.principal_id

    WHERE dprole.name IN ('db_owner', 'db_securityadmin')

    UNION ALL

    SELECT CR.member_id AS role_id,

    CR.member_name AS role_name,

    CR.major_principal_type,

    dpmembers.principal_id AS member_id,

    dpmembers.name AS member_name,

    dpmembers.type_desc AS minor_principal_type

    FROM CTE_Roles CR

    JOIN sys.database_role_members drm ON CR.member_id = drm.role_principal_id

    JOIN sys.database_principals dpmembers ON drm.member_principal_id = dpmembers.principal_id

    )

    SELECT role_id,

    role_name,

    major_principal_type,

    member_id,

    member_name,

    minor_principal_type

    FROM CTE_Roles

    WHERE member_name != 'dbo';

    @opc.three

    I found the script and as I'm a scripting newbie wasn't really sure. Thanks for your script I'll see if I can work out what it is doing - been a long time since I've done any programing!

Viewing 9 posts - 1 through 8 (of 8 total)

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