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|
|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.