Q207950: Using SQL queries to compare permissions between two accounts

This article will provide steps on how to pull fields from the databases to compare MGS member permission groups.

As an example:
There are 2 users that are set up. We'll call them user A and user B. User A is the only user that has correct permissions and has access to everything that is needed. On the other hand, user B is part of the same group as user A but still does not have access like user A.
Run SQL Profiler on the database and start RayManageSoft, navigate to the policies node, and then close RayManageSoft and stop the profiler run. Do this as both users. The start of the access rights check is this call:

exec ResourceGetByName @ResourceName=N'Policies'

Note the ResourceID returned by this query (when you run it in SQL Server Management Studio), it should be 15. Next execute:

exec UserGetBySAMAccountNameDomainFlatName @SAMAccountName=N'Administrator',@FlatName=N'VMWARE'

Substituting the users account name in the @SAMAccountName parameter and the flat from of their domain name in the @FlatName parameter (for example, the user in my example above is 'VMWARE\Administrator'). This will give back a UserID for that user. Remember this value and use it in the following query as the @TargetID parameter:

exec GroupGetAllInheritedGroupsWithMemberByTargetIDTargetTypeID @TargetID=2,@TargetTypeID=2

This returns a list of all groups that this user is a member of, from what they've said MGS Administrators should be listed here for both users. Take note of all the GroupID's returned and use them one by one as the @GroupID parameter in the following query:

exec RightGetAllByGroupIDResourceID @GroupID=177,@ResourceID=15

This will give the rights each group has to access policies. What you are looking for is that there is at least one group that offers ActionClassID = 2 (which is modify) and no group with Denied = 1. If you find a group which denies the access, the user will have to be made not a member of that group to correct the problem.


Powered by Zendesk