Enterprise PDM Transition Permit Permission by Group

By Charles Mayou

Recently I was testing a new workflow and I got to a point where I was unable to transition a file any further.  I started to review the groups and users in the workflow to figure out which had permission to permit the transition.   During this time I wondered if there was another way to view all transitions in order to verify Permit Permissions for either a user or a group.

  To simplify the process I was thinking about, I concentrated just on groups and then needed to verify that I had given the permission to at least one group for each transition. For this I needed to identify the information directly in the SQL Database.  I opened the database in Microsoft SQL Studio Management to review the GroupTransitionRights tables which I thought had all the information.  I found that I needed to review several tables to find the transition names and groups that had rights to each transition and the group names. Armed with the locations of information, I used a little SQL query building knowledge and created a query that combined the three tables that neatly and logically displayed the permit permissions.

SELECT     Transitions.Name AS [Transition Name], Groups.Groupname, Transitions.TransitionID, Workflows.NameAS [WorkFlow Name]FROM         Groups INNER JOIN

GroupTransitionRights ON Groups.GroupID = GroupTransitionRights.GroupID RIGHT OUTER JOIN

Transitions ON GroupTransitionRights.TransitionID = Transitions.TransitionID INNER JOIN

Workflows ON Transitions.WorkflowID = Workflows.WorkflowID

ORDER BY Transitions.TransitionID, Groups.Groupname

 

To display the query output results run the query from the Vault database in the Microsoft SQL Server Management Studio tool:  

Transition Name Groupname TransitionID WorkFlow Name
NULL NULL 1  
NULL NULL 2  
NULL NULL 3  
Hardlink NULL 4  
Parallel Approval (2/3 votes) NULL 5  
Yes from 2 out of 3 Management 6 Parallel Approval (2/3 votes)
Need 2 out of 3 Management 7 Parallel Approval (2/3 votes)
Request Approval Engineering 8 Parallel Approval (2/3 votes)
Request Approval Management 8 Parallel Approval (2/3 votes)
Approve (Person A) Management 9 Parallel Approval (2/3 votes)
Approve (Person B) Management 10 Parallel Approval (2/3 votes)
Approve (Person C) Management 11 Parallel Approval (2/3 votes)
Create new Revision Management 12 Parallel Approval (2/3 votes)

  The results neatly and logically display the column names; Transition Name, Groupname and WorkFlow Name which correspond to the permit permissions for each transition and group. As I built the query I realized there might be duplicate transition names and I needed a way to distinguish one from the other so that is why I included the WorkFlow Name column. With this list in hand I was able to go back and add the appropriate permit permission for the groups.  Now that I have saved the query I can open it again in the future to check that I assigned permit permission to at least one group for each transition.

Was this article helpful?
0 out of 0 found this helpful

Comments

0 comments

Please sign in to leave a comment.