Share via

How to determine resource licensing

  • Article

There are always several questions we face at customer sites on the licensing requirements for their Project Server implementation. It’s best to consult your local Microsoft Solution Sales Specialist (SSP) for the details to ensure compliance.

A common scenario we see customers are faced with is loading Project Server with ALL resources in the organization, whether work assignments exist or not, and have them available to perform work as it is assigned. An organization may likely consist of 10,000 people and only 1,200 active on projects at a time and would prefer to only have 1,200 Client Access License (CAL)s for the entire organization. This scenario is not allowed, however, Microsoft allows the CAL to be transferred (as a one off) after a 90 day period to a new user.

The simple SQL script below provides a view of all the resources in the named instance you set through the @PUBLISHED_DB_NAME variable to have the discussion with the SSP. Key columns to review from the SQL output that form a type of 3 way logic are:

  • RES_TYPE
  • LOGON_TO_PRO
  • LOGON_TO_PWA

RES_TYPE: The RES_TYPE_DESC case statement shows a RES_TYPE < 20 for users who can login to the server themselves and would require a CAL. A RES_TYPE between 20 and 99 cannot log themselves in and do not require a CAL. Any RES_TYPE 100 or higher are INACTIVE and do not require a CAL.

LOGON_TO_PRO: The LOGON_TO_PRO value is set in PWA by an administrator under Global Permissions for that user or category. To prevent logon from Project Professional requires the Deny option to be explicitly checked. This value is set the first time they login into the server and download the EGlobal. Therefore belonging to the Project Manager group itself does not qualify them as a Project Professional user.

LOGON_TO_PWA: The LOGON_TO_PWA value is set in PWA by an administrator under Global Permissions for that user or category. To prevent logon from to the Server requires the Deny option to be explicitly checked. A user who is denied access to login to the server does not require a CAL. E.g.: An active resource who has assignments in a project but never inputs, queries or views data on the server.

 

To summarize:

  1. For a resource to qualify for a PWA license it requires the RES_TYPE < 20 AND LOGON_TO_PWA = YES
  2. For a resource to qualify for a PRO license it requires the RES_TYPE < 20 AND LOGON_TO_PRO = YES AND LOGON_TO_PWA = YES
  3. If one or more of the conditions above is not true then the resource is INACTIVE/DENIED and no license is required

 

Click here for a sample view of the SQL Output.SQL_Output_Sample

SQL CODE

/* -- **************************************************** SUMMARY NOTES ****************************************************** -- RESOURCE SYSTEM REPORT V1.0     This report extracts a series of data points from the MSP_RESOURCES table in the Published Database with OUTER JOINS to     the MSP_RESOURCES table and OUTER JOINS to the MSP_WEB_FN_SEC_GetGlobalPermissionAccessInfo function table.     The data qualifies primary resource information for diagnostic analysis together with information to identify license     usage. This query will be included in the EPM Diagnostics Extract Tool. CUSTOMIZATION     1. Set local variable @WORKING_QUERY with the dynamic Select query to execute.     2. Set local variable @PUBLISHED_DB_NAME with your PUBLISHED database name.

CHANGELOG     12/16/2009 MJ: Prepared for release to EPM Diagnostics Tool

*/ -- ************************************************ BEGIN DECLARE VARIABLES ************************************************ -- DECLARE @WORKING_QUERY nvarchar(max)              -- Variable to contain SELECT statements DECLARE @PUBLISHED_DB_NAME nvarchar(100)          -- Variable to set the PUBLISHED database name ----------------------------------------------------  END DECLARE VARIABLES  --------------------------------------------------

-- *********************************************** BEGIN SET VARIABLE VALUES *********************************************** -- SET @PUBLISHED_DB_NAME = N'PWA_Published'          -- Set local variable @PUBLISHED_DB_NAME with your PUBLISHED database name SET @WORKING_QUERY = N'use ' + @PUBLISHED_DB_NAME + N';' + N' --------------------------------------------------  END SET VARIABLE VALUES  --------------------------------------------------- SELECT        rs.[RES_NAME]       ,rs.[WRES_ACCOUNT]       ,rs.[RES_TYPE]       ,CASE           -- ACTIVE RESOURCES HAVE RES_TYPE VALUE < 100           -- ACTIVE HUMAN RESOURCES HAVE RES_TYPE VALUE < 20     WHEN rs.[RES_TYPE] = 1 then ''PureUser - cannot be assigned''     WHEN rs.[RES_TYPE] = 2 then ''WorkResource - can be assigned''     WHEN rs.[RES_TYPE] = 20 then ''GenericWorkResource - cannot login themselves''     WHEN rs.[RES_TYPE] = 21 then ''MaterialResource - cannot login themselves''     WHEN rs.[RES_TYPE] = 22 then ''GenericMaterialResource - cannot login themselves''     WHEN rs.[RES_TYPE] = 25 then ''CostResources - cannot login themselves''     WHEN rs.[RES_TYPE] = 26 then ''GenericCostResources - cannot login themselves''     WHEN rs.[RES_TYPE] = 50 then ''BudgetWorkResource - cannot login themselves''     WHEN rs.[RES_TYPE] = 51 then ''BudgetCostResource - cannot login themselves''     WHEN rs.[RES_TYPE] = 52 then ''BudgetMaterialResource - cannot login themselves''     WHEN rs.[RES_TYPE] = 53 then ''GenericBudgetWorkResource - cannot login themselves''     WHEN rs.[RES_TYPE] = 54 then ''GenericBudgetCostResource - cannot login themselves''     WHEN rs.[RES_TYPE] = 55 then ''GenericBudgetMaterialResource - cannot login themselves''     -- INACTIVE RESOURCES HAVE 100 ADDED TO THE RES_TYPE VALUE     WHEN rs.[RES_TYPE] = 101 then ''PureUser - Inactive''     WHEN rs.[RES_TYPE] = 102 then ''WorkResource - Inactive''     WHEN rs.[RES_TYPE] = 120 then ''GenericWorkResource - Inactive''     WHEN rs.[RES_TYPE] = 121 then ''MaterialResource - Inactive''     WHEN rs.[RES_TYPE] = 122 then ''GenericMaterialResource - Inactive''     WHEN rs.[RES_TYPE] = 125 then ''CostResources - Inactive''     WHEN rs.[RES_TYPE] = 126 then ''GenericCostResources - Inactive''     WHEN rs.[RES_TYPE] = 150 then ''BudgetWorkResource - Inactive''     WHEN rs.[RES_TYPE] = 151 then ''BudgetCostResource - Inactive''     WHEN rs.[RES_TYPE] = 152 then ''BudgetMaterialResource - Inactive''     WHEN rs.[RES_TYPE] = 153 then ''GenericBudgetWorkResource - Inactive''     WHEN rs.[RES_TYPE] = 154 then ''GenericBudgetCostResource - Inactive''     WHEN rs.[RES_TYPE] = 155 then ''GenericBudgetMaterialResource - Inactive''     ELSE ''RES_TYPE not found''         END AS RES_TYPE_DESC       ,(CASE WHEN logonProPerm.WSEC_DENY = 0 THEN ''YES'' ELSE ''NO'' END) AS LOGON_TO_PRO       ,(CASE WHEN logonPerm.WSEC_DENY = 0 THEN ''YES'' ELSE ''NO'' END) AS LOGON_TO_PWA       ,(CASE WHEN rs.[RES_IS_WINDOWS_USER] = 1 THEN ''YES'' ELSE ''NO'' END) AS RES_IS_WINDOWS_USER       ,rs.[WRES_LAST_CONNECT_DATE]       ,rs.[CREATED_DATE]       ,getdate() AS RECORDED_DATE FROM [dbo].[MSP_RESOURCES] rs       LEFT OUTER JOIN [dbo].[MSP_RESOURCES] AS tms ON rs.[RES_TIMESHEET_MGR_UID] = tms.[RES_UID]       LEFT OUTER JOIN [dbo].[MSP_RESOURCES] AS assn ON rs.[RES_DEF_ASSN_OWNER] = assn.[RES_UID]       LEFT OUTER JOIN dbo.MSP_WEB_FN_SEC_GetGlobalPermissionAccessInfo(''D2F88263-31C5-4FF5-BEAE-B98B26C5C116'') logonProPerm ON rs.RES_UID = logonProPerm.RES_UID     LEFT OUTER JOIN dbo.MSP_WEB_FN_SEC_GetGlobalPermissionAccessInfo

(''7C40A2C2-FD15-44E3-8FD3-11B3E0A4E441'') logonPerm ON rs.RES_UID = logonPerm.RES_UID ORDER BY rs.[RES_TYPE], LOGON_TO_PRO DESC, LOGON_TO_PWA DESC     '

EXEC(@WORKING_QUERY)

Comments

  • Anonymous
    September 21, 2015
    Has anyone figured out how to modify this for MS Project 2013?