User Roles

This script can be run for any group of people - a Tag, Organization members, Search Builder Results, a sub-group in an Organization. It is designed to run from the blue Toolbar on those pages. However, you must choose to add it to the toolbar after you run the script from Special Content.

The report will include only those from your list that actually have some type of user account. It the person is listed on the report and there are no Roles listed, he has a My Data account.

Use Case - Staff

Use a search to find all your staff members, assuming you have them either in organizations or they have an Extra Value. Then run this report to verify their roles.

You can also run a search to find those Previous members of your staff orgs and see who might still have a user account and no longer needs one. You can make those people My Data users.

Use Case - Lay Leaders

Create a search to find those those enrolled in a specific Program and Division and who have a Member Type of Teacher, In-Reach Leader, or any other leader type. Add the Condition Is System User = true.

Then run this report from the search results to see which of your leaders need an OrgLeaderOnly user account. Perhaps they have a My Data account and just need OrgLeadersOnly added to there existing account. Or maybe they have OrgLeadersOnly but need Attendance in order to take attendance using the App or the web version of the application.

The PID# is a link directly to the person’s record, so if the system Admin is running the report he can go directly to the person’s record and create the appropriate user account.

Remember, the report will list only those with existing user accounts. So, in the above use case for Lay Leaders, if you want to find those without any type of user account change that 2nd condition Is System User from true to false. Then you can run the search, save it, and send the name of that saved search to your system Admin and ask for OrgLeadersOnly accounts be created for all of them.

If a person has more than one user account, he will be listed twice. Normally, only an Admin should ever need multiple user accounts.

Sample User Roles Report

SQL Code for User Roles Report

The recommended name is UserRoles and is viewable by those with Edit role.


You can change the role in the script itself in line 1. Replace Edit with Admin or some other role.

Use the following code to create the SQL Script. If you are not familiar with creating scripts, see the help article below.

See How to create a SQL Script.

SELECT p.PeopleId
       ,( SELECT    STUFF(
      (SELECT   N',' + r.RoleName
       FROM     dbo.UserRole ur
                JOIN dbo.Roles r ON r.RoleId = ur.RoleId
       WHERE    ur.UserId = u.UserId
                    FOR   XML PATH(''), TYPE)
      .value('text()[1]', 'nvarchar(max)'), 1, 1, N'')
        ) Roles
FROM    Users u
        JOIN dbo.People p ON p.PeopleId = u.PeopleId
        JOIN dbo.TagPerson tp ON tp.PeopleId = p.PeopleId AND tp.Id = @qtagid

Latest Update


Modify image link with secure protocol.