Question / Problem:
When a user has inadvertently been given or denied a permission, how is it possible to determine which group is contributing the problem permissions to the user?
Answer / Solution:
In an environment that has many users and many groups, and especially when users belong to many groups, it can be difficult to determine why a user has been given or denied a certain permission. In addition to permissions directly assigned to a user, a user will have the lowest permission from the collection of their groups, so it might be needed to look at the permissions associated with each group that the user belongs to. To help troubleshoot a situation like this, a diagnostic query can help see groups that are contributing permissions to a specific user.
To use this query, set the @UserName variable to the name of the user that needs to be investigated, then execute the SQL script against the main TotalAgility database to see permissions from the user itself, as well as each group that the user is in.
Download SQL script: PermissionsFromGroup.sql
--With a large number of groups, it can be difficult to determine why a user has been given or denied a certain permission. --This query will show the permissions from a user and each group the user is in, to help troubleshoot. --Set the name of the user to query permissions for: DECLARE @UserName NVARCHAR(256) SET @UserName='kofax' DECLARE @UserID BINARY(16) SELECT @UserID=r.RESOURCE_ID FROM AW_RESOURCE AS r WHERE r.RESOURCE_NAME=@UserName SELECT CASE r.RESOURCE_TYPE WHEN 0 THEN 'User' WHEN 3 THEN 'Group' ELSE 'Unknown' END AS PermissionFromType, r.RESOURCE_NAME AS PermissionFrom, CONVERT(NVARCHAR(10),ace.ACL_TYPE) + ': ' + CASE ace.ACL_TYPE WHEN 0 THEN 'Resource' WHEN 1 THEN 'MaintainServer' WHEN 2 THEN 'Simulation' WHEN 3 THEN 'BusinessProcess' WHEN 4 THEN 'LibraryItem' WHEN 5 THEN 'ProcessVarTemplate' WHEN 6 THEN 'WorkQueueDefinition' WHEN 7 THEN 'LockStatusSearch' WHEN 8 THEN 'BusinessRule' WHEN 9 THEN 'LiveSession' WHEN 10 THEN 'PurgeJobData' WHEN 11 THEN 'Dashboards' WHEN 12 THEN 'DeleteDocuments' WHEN 13 THEN 'MaintainDocumentAnnotation' WHEN 14 THEN 'RejectDocumentsPages' WHEN 15 THEN 'OverrideProblems' WHEN 16 THEN 'OnlineLearning' WHEN 17 THEN 'ChangeDocumentClass' WHEN 18 THEN 'OtherBatchEditingOperations' WHEN 19 THEN 'AllowAddFolderCmd' WHEN 20 THEN 'ManagePersonas' WHEN 21 THEN 'DeletePages' WHEN 22 THEN 'DeleteFolders' WHEN 23 THEN 'AllowSplitOnBackPage' WHEN 24 THEN 'SplitDocument' WHEN 25 THEN 'RejectPages' WHEN 26 THEN 'IdeAccess' WHEN 27 THEN 'Device' WHEN 28 THEN 'CaptureDesign' WHEN 29 THEN 'RotatePages' WHEN 30 THEN 'CreateDocumentsAndPages' WHEN 31 THEN 'ModifyDocument' WHEN 32 THEN 'ConfirmFields' WHEN 33 THEN 'MergeDocuments' WHEN 34 THEN 'MaskAndRedactImages' WHEN 35 THEN 'WebCaptureMaskAndRedactImages' ELSE 'Unknown' END AS PermissionType, CONVERT(NVARCHAR(10),ace.ACCESS_PERMISSION) + ': ' + CASE ace.ACCESS_PERMISSION WHEN 0 THEN 'FullControl' WHEN 1 THEN 'ReadWrite' WHEN 2 THEN 'Read' WHEN 3 THEN 'NoAccess' WHEN 4 THEN 'HasAccess' WHEN 5 THEN 'LimitedToOnlineLearning' ELSE 'Unknown' END AS Permission FROM ( SELECT GROUP_RESOURCE_ID FROM GROUP_MEMBERS AS gm WHERE gm.MEMBER_RESOURCE_ID=@UserID UNION SELECT @UserID ) AS UserAndGroups LEFT JOIN AW_RESOURCE AS r ON r.RESOURCE_ID=UserAndGroups.GROUP_RESOURCE_ID LEFT JOIN ACCESS_CONTROL_ENTRY AS ace ON ace.RESOURCE_ID=r.RESOURCE_ID --User permissions first, then group in order of group name: ORDER BY PermissionFromType DESC, PermissionFrom --Order by type of permission: --ORDER BY ace.ACL_TYPE, Permission, PermissionFrom