Home SQL Server

How to use SQL on ServiceManager DB to check User Permissions from User Roles

Conner_WoodConner_Wood Customer Ninja IT Monkey ✭✭✭✭
edited October 2016 in SQL Server
Hi all,

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....:
  1. ProfileOperationImplicitScope
  2. UserRoleUserAssignment (It's got a ScopeId column)
  3. UserRoleGroupType
  4. UserRoleScopeHandle (It's got a ScopeId column)
  5. UserRoleScopeView

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:
  • ActivityImplementer
  • AdvancedOperator
  • Author
  • ChangeInitiator
  • ChangeManager
  • EndUser
  • IncidentResolver
  • Operator
  • ProblemAnalyst
  • ReadOnlyOperator
  • ReleaseManager
  • ServiceRequestAnalyst
  • Workflow

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.

Best Answers

Answers

  • Conner_WoodConner_Wood Customer Ninja IT Monkey ✭✭✭✭
    To put it simply, I need to know how SCSM shows the users/groups of the User Role from the ServiceManager database in the SCSM Console.  Somehow it's being stored in the database, I just need to know where and how.

    I know Travis Wright had contributed a few things for User Roles, so I'm researching to see if I can learn from it:

    I have to admit this is far more difficult than I thought it would be for something as simple as showing the members in a user role.
  • merlenette_jonesmerlenette_jones Member Advanced IT Monkey ✭✭✭
    Hello Conner,

    I see you are putting a lot of work into this. I'm quite impressed. If I understand you correctly, you are trying to figure out the schema of how the user to user-role relationship is stored in the SCSM DB?

    Can you elaborate on your purpose for this?

    Merle
Sign In or Register to comment.