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.
Comments
Please sign in to leave a comment.