How to use SQL on ServiceManager DB to check User Permissions from User Roles
I am completely stuck regarding having a user/group and generating a list of all roles they are in and then seeing if they are part of certain "Operations"
SELECT PO.[ProfileOperationId] ,PO.[ProfileId] ,UR.[UserRoleId] ,P.[ProfileName] ,UR.[UserRoleName] ,O.[OperationName] ,O.[OperationNumericId] FROM [ServiceManager].[dbo].[ProfileOperation] PO JOIN [ServiceManager].[dbo].[Operation] O ON O.OperationId = PO.OperationId JOIN [ServiceManager].[dbo].[Profile] P ON P.[ProfileId] = PO.[ProfileId] JOIN [ServiceManager].[dbo].[UserRole] UR ON UR.[ProfileId] = P.[ProfileId] WHERE O.[OperationName] IN ( 'User_IsAdministrator__Check' ,'User_IsIncidentResolver__Check' ,'User_IsServiceRequestAnalyst__Check' ,'User_IsChangeManager__Check' ,'User_IsProblemAnalyst__Check' ,'User_IsReleaseManager__Check' ) ORDER BY O.[OperationNumericId]
This gives me a piece of the information I require as this allows me to have all the UserRoleIds or ProfileIds to match against a generated list from a user/group.
However when it comes to getting the User Roles for a particular user, I query the UserRole table which seems like the right thing to do, yet all the rows returned for a user point to some "UserRoleImplied~~~~" prefixed User Role.
So naturally I've been joining all sorts of tables together but I still can't figure out what the following tables do and if I need them somehow and how to use them....:
- UserRoleUserAssignment (It's got a ScopeId column)
- UserRoleScopeHandle (It's got a ScopeId column)
The farthest I've gotten is getting some roles from the [ProfileOperationImplicitScope] table, but if the user is in the Administrators Role in SCSM, I cannot seem to find out how to query it.
Ex. In User Role "Administrators" there is "SCSM Service Account" which gives me UserId of "21002" from the [UserRoleUser] table. This account is only a member of the "Administrators" User Role in the SCSM Console. On my first join to [Profile] I only get back 'ImpliedUserPreference'. I join on the same column names and I join the tables as follows.
[UserRoleUserAssignment] --> [UserRole] --> [Profile] --> [ProfileOperationImplicitScope] --> [ProfileOperation] --> [Profile]
The list of Profiles that I get are:
However it's not showing the Administrator Profile which I need it to be able to do, so frustrating!!!!!!
I feel it shouldn't have to be this hard but I can't really figure out the solution. I figured Cireson is most familiar with User Roles and perhaps can shed some light on this.