Example SQL script for editing user management in QPR ProcessAnalyzer database

This is an example SQL script which modifies user management objects in QPR ProcessAnalyzer datatabase. The script does the following:

1. Creates a new group <All users> (if doesn't already exist).
2. Adds the Viewer role for project QPR ProcessAnalyzer UI to the created group. (The script throws an error if QPR ProcessAnalyzer UI project doesn't exist.)
3. Adds all users to the created group.

DECLARE @groupName NVARCHAR(MAX);  
SET @groupName = '<All users>';  

IF (NOT EXISTS (SELECT [PRO_ID] FROM [dbo].[PA_PROJECT] WHERE PRO_NAME = 'QPR ProcessAnalyzer UI'))
	THROW 51000, 'Project "QPR ProcessAnalyzer UI" is missing.', 1;  
	
--Create the group and add Viewer role for project "QPR ProcessAnalyzer UI"
IF NOT EXISTS (SELECT 1 FROM [dbo].[PA_USER] WHERE USR_FULL_NAME = @groupName)
BEGIN
	INSERT INTO [dbo].[PA_USER] ([USR_LOGON_NAME], [USR_PASSWORD_HASH], [USR_EMAIL], [USR_DESCRIPTION], [USR_FLAGS], [USR_FULL_NAME])
	VALUES ('Group_' + @groupName, '', '', 'Group that should contain all users', 1, @groupName)
	
	INSERT INTO [dbo].[PA_USER_ROLE] ([URO_PROJECT_ID], [URO_USER_ID], [URO_ROLE_ID]) VALUES (
		(SELECT [PRO_ID] FROM [dbo].[PA_PROJECT] WHERE PRO_NAME = 'QPR ProcessAnalyzer UI'),
		(SELECT USR_ID FROM [dbo].[PA_USER] WHERE USR_FULL_NAME = @groupName),
		6
	)
END

-- Add all users to the group
INSERT INTO [dbo].[PA_USER_GROUP] (USG_GROUP_ID, USG_MEMBER_ID, USG_ROLE_ID)
SELECT 
	(SELECT USR_ID FROM [dbo].[PA_USER] WHERE USR_FULL_NAME = @groupName),
	u.[USR_ID],
	1
FROM [dbo].[PA_USER] u
WHERE
	(u.[USR_FLAGS] = 0 OR u.[USR_FLAGS] IS NULL) AND
	u.[USR_ID] NOT IN (
		SELECT USG_MEMBER_ID FROM [dbo].[PA_USER_GROUP]
		WHERE USG_GROUP_ID = (SELECT USR_ID FROM [dbo].[PA_USER] WHERE USR_FULL_NAME = @groupName)
	);