BES User Role Listing Script

Joolie · #1 (**permalink**) 01-20-2009, 09:08 AM

I would like a simple script to list the users that are assigned to specific roles on the BES; i.e, all users assigned the role of rim_db_admin_sr_helpdesk.

Thanks

hdawg · #2 (**permalink**) 01-20-2009, 11:30 PM

Try this:

EXEC sp_helpuser 'rim_db_admin_sr_helpdesk'
EXEC sp_helpuser 'rim_db_admin_jr_helpdesk'

adding a line for each role you want to report on

Joolie · #3 (**permalink**) 01-21-2009, 08:07 AM

Perfect! Thanks

EDIT: Wait, not so perfect. It's only returning the results of whatever is in the first line. I use 2 different roles, so mine looks like this:

EXEC sp_helpuser 'rim_db_admin_security'
EXEC sp_helpuser 'rim_db_admin_sr_helpdesk'

When I run it like that, I get 2 records back (2 people have the admin_security role).

If I put the sr_helpdesk line first, then I only get the people that have that role, and not the admin_security role.

hdawg · #4 (**permalink**) 01-21-2009, 05:01 PM

Try either of these; should give you exactly what you want.

Code:

SELECT p.NAME As Role, m.NAME As Username
FROM sys.database_role_members rm
JOIN sys.database_principals p
ON rm.role_principal_id = p.principal_id
JOIN sys.database_principals m
ON rm.member_principal_id = m.principal_id
WHERE p.NAME LIKE 'rim%'

Code:

SELECT
        [Login Type]=
        case sp.type
        when 'u' then 'WIN'
        when 's' then 'SQL'
        when 'g' then 'GRP'
        end,
        convert(char(45),sp.name) as srvLogin, 
        convert(char(45),sp2.name) as srvRole,
        convert(char(25),dbp.name) as dbUser,
        convert(char(25),dbp2.name) as dbRole
FROM
        sys.server_principals as sp join
        sys.database_principals as dbp on sp.sid=dbp.sid join
        sys.database_role_members as dbrm on dbp.principal_Id=dbrm.member_principal_Id join
        sys.database_principals as dbp2 on dbrm.role_principal_id=dbp2.principal_id left join 
        sys.server_role_members as srm on sp.principal_id=srm.member_principal_id left join
        sys.server_principals as sp2 on  srm.role_principal_id=sp2.principal_id

Joolie · #5 (**permalink**) 01-21-2009, 06:30 PM

Thanks, Monkey, I will try that out and let you know how it goes.

hdawg · #6 (**permalink**) 01-21-2009, 10:08 PM

Quote:

Originally Posted by Joolie

Thanks, Monkey, I will try that out and let you know how it goes.

Glad to help ... hopefully

ecnavt · #7 (**permalink**) 02-02-2009, 04:16 PM

The first one worked perfectly for me. Thanks for posting that!

Mark_Venture · #8 (**permalink**) 02-07-2011, 11:56 AM

What version BES is this for?

I get errors when I try those posted above, and when manually looking at a copy of the BESMgmt database, I can't find those fields/tables.

How do you do this for BES 4.1sp7 (and soon to be BES5)?

Joolie · #9 (**permalink**) 02-07-2011, 11:59 AM

I'm using the first script on BES 4.1.7.

What tool are you using to run the script? I'm using Query Express.

Mark_Venture · #10 (**permalink**) 02-10-2011, 03:12 PM

Quote:

Originally Posted by Joolie

I'm using the first script on BES 4.1.7.

What tool are you using to run the script? I'm using Query Express.

Query Express v3.9.

The commands in post 2 & 3 of this thread, works with results like you state in post 3.

the first script in post #4 results in...

Invalid object name 'sys.database_role_members'.
Invalid object name 'sys.database_principals'.
Invalid object name 'sys.database_principals'.

the second script in post #4 results in...

Invalid object name 'sys.server_principals'.
Invalid object name 'sys.database_principals'.
Invalid object name 'sys.database_role_members'.
Invalid object name 'sys.database_principals'.
Invalid object name 'sys.server_role_members'.
Invalid object name 'sys.server_principals'.

Follow Us On

Forum Sponsors

Google Advertisements