Welcome to Port3101.org : Your BES Connection Mark forums read | View Forum Leaders
Port3101.org : Your BES Connection



Reply
LinkBack Thread Tools Display Modes
BES User Role Listing Script
 
  #1 (permalink)  
Old 01-20-2009, 10:08 AM
Joolie's Avatar
Super Moderator
 
Join Date: Dec 2008
Location: West Michigan
Posts: 763
Default BES User Role Listing Script

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
__________________
You may know me as Juwaack68
Reply With Quote
Sponsored Links
  #2 (permalink)  
Old 01-21-2009, 12:30 AM
hdawg's Avatar
Proprietor
 
Join Date: Nov 2008
Posts: 2,257
Blog Entries: 147
Default

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
__________________
http://blog.port3101.org/hdawg/
Reply With Quote
  #3 (permalink)  
Old 01-21-2009, 09:07 AM
Joolie's Avatar
Super Moderator
 
Join Date: Dec 2008
Location: West Michigan
Posts: 763
Default

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.
__________________
You may know me as Juwaack68

Last edited by Joolie; 01-21-2009 at 09:13 AM.
Reply With Quote
  #4 (permalink)  
Old 01-21-2009, 06:01 PM
hdawg's Avatar
Proprietor
 
Join Date: Nov 2008
Posts: 2,257
Blog Entries: 147
Default

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
__________________
http://blog.port3101.org/hdawg/
Reply With Quote
  #5 (permalink)  
Old 01-21-2009, 07:30 PM
Joolie's Avatar
Super Moderator
 
Join Date: Dec 2008
Location: West Michigan
Posts: 763
Default

Thanks, Monkey, I will try that out and let you know how it goes.
__________________
You may know me as Juwaack68
Reply With Quote
  #6 (permalink)  
Old 01-21-2009, 11:08 PM
hdawg's Avatar
Proprietor
 
Join Date: Nov 2008
Posts: 2,257
Blog Entries: 147
Default

Quote:
Originally Posted by Joolie View Post
Thanks, Monkey, I will try that out and let you know how it goes.
Glad to help ... hopefully
__________________
http://blog.port3101.org/hdawg/
Reply With Quote
  #7 (permalink)  
Old 02-02-2009, 05:16 PM
BES Activated
 
Join Date: Feb 2009
Location: Seattle, WA
Posts: 1
Default

The first one worked perfectly for me. Thanks for posting that!
Reply With Quote
  #8 (permalink)  
Old 02-07-2011, 12:56 PM
BES Administrator
 
Join Date: Jan 2009
Location: Wilmington, DE
Posts: 14
Default

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)?
Reply With Quote
  #9 (permalink)  
Old 02-07-2011, 12:59 PM
Joolie's Avatar
Super Moderator
 
Join Date: Dec 2008
Location: West Michigan
Posts: 763
Default

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.
__________________
You may know me as Juwaack68
Reply With Quote
  #10 (permalink)  
Old 02-10-2011, 04:12 PM
BES Administrator
 
Join Date: Jan 2009
Location: Wilmington, DE
Posts: 14
Default

Quote:
Originally Posted by Joolie View Post
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'.

Last edited by Mark_Venture; 02-10-2011 at 04:16 PM.
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Script to grab user / handheld data hdawg Scripts & Tools / Hints & Tricks 15 10-04-2010 02:47 PM
BES Role Management Zippygit Port 3101: The BES Admin Bar & Grill 3 06-08-2010 03:44 PM
Command-line Script for User Manipulation via BUA Otto Scripts & Tools / Hints & Tricks 3 02-03-2010 11:50 PM
Script for listing applications installed on Blackberries AUTiger92 Scripts & Tools / Hints & Tricks 11 08-12-2009 03:00 PM
SQL Script to search for user by phone number Snapp Scripts & Tools / Hints & Tricks 5 06-03-2009 08:08 PM


All times are GMT -4. The time now is 02:19 PM.
Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2018, vBulletin Solutions, Inc.


 

SEO by vBSEO 3.3.2 PL2