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.
Note
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.
--Roles=Edit
SELECT p.PeopleId
,p.Name
,u.Username
,( 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 |
11/13/2020 |
Modify image link with secure protocol.