Andrew's Business Intelligence Blog

Today while setting up my training environment for class I ran into this neat error.  I figured the problem was that I removed the model in which the user was assigned to the modeler role before first removing the role. This is what you get when trying to remove the user from the Users section of the Administration Console:

I found that in the back-end the user was still attached to a role of modeler for the model sub-site I had removed. By running the following query I was able to see what roles the user was still attached to, which in this case was only one role. Here is the query to get the UserRoleAssignmentId,RoleId, and UserId associated to a users alias.

--Get all of a users given Roles
SELECT a.[UserRoleAssignmentID]
      ,a.[RoleID]
      ,a.[UserID]
      ,u.[UserName]
  FROM [PPSPlanningSystem].[dbo].[Users_Roles] a
INNER JOIN dbo.BizUsers u on u.[UserId] = a.[UserId]
WHERE u.[UserName] = 'DOMAIN\username'

After determining the UserRoleAssignmentID I was able to remove the user to role association with the stored procedure in PPSPlanningSystem

--Execute to remove all roles from a given user
DECLARE @RC int
DECLARE @UserRoleAssignmentID bigint

EXECUTE @RC = [PPSPlanningSystem].[dbo].[bsp_BizUserRoleDeleteByAssignmentID] 
   @UserRoleAssignmentID='1767'

And finally remove the user from the system using this stored procedure.

--Now you can remove the user from within the admin console or via the store procedeure
DECLARE @RC int
DECLARE @UserID bigint

EXECUTE @RC = [PPSPlanningSystem].[dbo].[bsp_BizUserDelete] 
   @UserID='219'

Interestingly even after you remove the all the roles for a user you still receive the submission error through the console, but the back-end end executes without error and indeed removes the user from the console. If you try to execute the proc to remove the user before removing the role you will receive the following error:

Msg 547, Level 16, State 0, Procedure bsp_BizUserDelete, Line 15
The DELETE statement conflicted with the REFERENCE constraint "FK1_BizUsers_UsersRoles". The conflict occurred in database "PPSPlanningSystem", table "dbo.Users_Roles", column 'UserID'.
The statement has been terminated.
Msg 50016, Level 16, State 5, Procedure bsp_BizUserDelete, Line 19
Module(Security)SP(bsp_BizUserDelete)ErrSeq#(5): GRANT statement failed at (BizUsers).

-Andy

I was recently asked about how to migrate security from one environment to another, I figured what a better way than to post a blog about it. In order to do this you will need at least three things: two CSV files in a format in which the PPSCmd.exe can interpret and a batch file to properly execute the PPSCmd executable.

You must have at minimum read permissions to the [PPSPlanningSystem] database on the server, and be granted user/data administrator roles in the PPS admin console.

Now for the example:

--Query used to generate users.csv
SELECT c.[UserLabel] AS [Label]
      ,c.[UserName] AS [Name]
      ,c.[EmailAddress] AS [Email]
  FROM [PPSPlanningSystem].[dbo].[BizUsers] c

--Query used to generate userroles.csv
SELECT DISTINCT 
      b.[UserLabel] AS [User]
	,c.[RoleLabel] AS [Role]
	,'ApplicationName' AS [Application] --Application in Business Modeler Site Summary
	,'ApplicationLabelName' AS [Scope] --Label in Business Modeler Site Summary
 FROM [PPSPlanningSystem].[dbo].[Users_Roles] a
 INNER JOIN [PPSPlanningSystem].[dbo].[BizUsers] b ON b.[UserID] = a.[UserID]
 INNER JOIN [PPSPlanningSystem].[dbo].[BizRoles] c ON c.[RoleID] = a.[RoleID]
ORDER BY 2

After generating the two CSV files you are ready to import them, I have created a batch script to do this for me my file looks like the following:

@echo off
@echo Importing Application Security...

SET /p SERVER="Enter PPS web service machine name: "
SET /p PORT="Enter PPS web service port: "
SET /p CSVPATH="Enter Path to CSV Files: ie:C:\Security :"

@echo Importing Users...
ppscmd importsecurity /server http://%SERVER%:%PORT% /type user %CSVPATH%\users.csv
pause
@ECHO Importing Users to Roles...
ppscmd importsecurity /server http://%SERVER%:%PORT% /type userrole %CSVPATH%\userroles.csv
pause

Note that I have just ppscmd in my file, I have added the full path for the PPSCmd to my Path variables in System Properties > Environment Variables> Path default is C:\Program Files\Microsoft Office PerformancePoint Server\3.0\BizModeler\

-Andy

• Exporting and Importing security (Security migration) for PPS2007.
• How to not break the server by changing the server name / domain in which server resides.
• How to easily migrate reports without manually exporting/importing each one individually.
• SSIS packages to execute a PPSCMD command and migrate an application.
• Using T-SQL views and store procedures in conjunction with SSIS to add, update and delete members from dimensions, hierarchies.
• How to slowly change your models structure.

 I have a lot of information with little spare time to actually post it but I will be working hard to try and get it all out there.