Query to List PWA Groups with Resources

blank

Query to List PWA Groups with Resources

Version 4.1, Modified: 09/03/2018

Applies To: Project Server 2007, 2010, 2013, 2016


The following query gives you list of groups with users, their NT accounts, email address & some other details in Project Web App. I’ve used the query to get list of all users that have Project Manager / Resource Manager access or part of similar custom groups. These can be often helpful in getting the list of users that might have MS Project Professional installed on their desktops for licensing counts.

MS Project Server 2016 Version

-- Project Web App 2016 SQL Query to List PWA Groups with Resources
--by: Faisal Masood (www.softvative.com)
--use WSS_Content_Project
--select * from pjpub.MSP_WEB_SECURITY_GROUPS
--select * from pjpub.MSP_WEB_SECURITY_GROUP_MEMBERS
--select * from pjpub.MSP_RESOURCES


SELECT pjpub.MSP_WEB_SECURITY_GROUPS.WSEC_GRP_NAME, pjpub.MSP_WEB_SECURITY_GROUPS.WSEC_GRP_AD_GROUP, pjpub.MSP_RESOURCES.RES_NAME, pjpub.MSP_RESOURCES.WRES_EMAIL, pjpub.MSP_RESOURCES.WRES_ACCOUNT, 
pjpub.MSP_WEB_SECURITY_CLAIMS.ENCODED_CLAIM, pjpub.MSP_RESOURCES.WRES_LAST_CONNECT_DATE, pjpub.MSP_WEB_SECURITY_CLAIMS.SECURITY_GUID, 
pjpub.MSP_WEB_SECURITY_GROUP_MEMBERS.WRES_GUID, pjpub.MSP_WEB_SECURITY_GROUPS.WSEC_GRP_GUID
FROM pjpub.MSP_WEB_SECURITY_GROUPS INNER JOIN
pjpub.MSP_WEB_SECURITY_GROUP_MEMBERS ON pjpub.MSP_WEB_SECURITY_GROUPS.WSEC_GRP_GUID = pjpub.MSP_WEB_SECURITY_GROUP_MEMBERS.WSEC_GRP_GUID INNER JOIN
pjpub.MSP_RESOURCES INNER JOIN
pjpub.MSP_WEB_SECURITY_CLAIMS ON pjpub.MSP_RESOURCES.WRES_ACCOUNT = pjpub.MSP_WEB_SECURITY_CLAIMS.ENCODED_CLAIM ON 
pjpub.MSP_WEB_SECURITY_GROUP_MEMBERS.WRES_GUID = pjpub.MSP_WEB_SECURITY_CLAIMS.SECURITY_GUID
ORDER BY pjpub.MSP_WEB_SECURITY_GROUPS.WSEC_GRP_NAME, pjpub.MSP_RESOURCES.RES_NAME

 

MS Project Server 2013 Version

-- MSPS 2013 SQL Query to List PWA Groups with Resources
--by: Faisal Masood (www.softvative.com) PMP, CCNA, ITIL, MCSE, MCITP, MCTS, MCSA, MOS, MS

-- use PPM_ProjectWebAppInstance
SELECT pub.MSP_WEB_SECURITY_GROUPS.WSEC_GRP_NAME, pub.MSP_RESOURCES.RES_NAME, 

pub.MSP_RESOURCES.WRES_EMAIL, pub.MSP_RESOURCES.WRES_ACCOUNT,
pub.MSP_RESOURCES.WRES_LAST_CONNECT_DATE, 

pub.MSP_WEB_SECURITY_CLAIMS.SECURITY_GUID,
pub.MSP_WEB_SECURITY_GROUP_MEMBERS.WRES_GUID, 

pub.MSP_WEB_SECURITY_GROUPS.WSEC_GRP_GUID
FROM pub.MSP_WEB_SECURITY_GROUPS INNER JOIN
pub.MSP_WEB_SECURITY_GROUP_MEMBERS ON
pub.MSP_WEB_SECURITY_GROUPS.WSEC_GRP_GUID = 

pub.MSP_WEB_SECURITY_GROUP_MEMBERS.WSEC_GRP_GUID INNER JOIN
pub.MSP_RESOURCES INNER JOIN
pub.MSP_WEB_SECURITY_CLAIMS ON pub.MSP_RESOURCES.WRES_ACCOUNT = 

pub.MSP_WEB_SECURITY_CLAIMS.ENCODED_CLAIM ON
pub.MSP_WEB_SECURITY_GROUP_MEMBERS.WRES_GUID = 

pub.MSP_WEB_SECURITY_CLAIMS.SECURITY_GUID
where WSEC_GRP_NAME like 'Administrators'
OR WSEC_GRP_NAME like 'Portfolio Managers'
OR WSEC_GRP_NAME like 'Project Managers'
OR WSEC_GRP_NAME like 'Resource Managers'
Order by
WSEC_GRP_NAME, RES_NAME

Click here to download the 2013 version query.

 

MSP2013_SQLQuery_Designer_for_Resources_and_Associated_Groups
MSP2013 SQLQuery Designer for Resources and Associated Groups

 

 

MS Project Server 2010 Version

— MSPS – Query to Finds PWA Security Groups with Active Users of those groups
–use ProjectServer_Published
select sg.Wsec_GRP_Name as GroupName, –mr.Wres_ID,
mr.Res_name as ResourceName, mr.Wres_NT_Account as NTAccount, mr.Wres_email as Email,
mr.Wres_Is_Enabled as IsEnabled, mr.Wres_Can_Login as CanLogin,
mr.Wres_Last_Connect as LastConnect, mr.Wres_is_Manager as IsManager
from msp_web_security_groups sg
inner join msp_web_security_group_members sgm on sg.Wsec_grp_guid = sgm.Wsec_grp_guid
inner join msp_web_resources mr on sgm.Wres_GUID = mr.Wres_GUID
where 
sg.WSec_grp_name like ‘Project Managers’ or
sg.WSec_grp_name like ‘Resource Managers’ or
sg.WSec_grp_name like ‘IT Administrators’ AND
mr.Wres_Is_Enabled = ‘1’
order by 
sg.wsec_grp_name, mr.res_name


Find users that have Permissions or Categories directly assigned

— MSP Query to Find users that have Permissions or Categories Directly Assigned
use [ProjectServer_Published]
  select
      u.res_name,
      case when c.wsec_cat_name is null
          then ‘Permission directly assigned to user’
          else ‘Category directly assigned to user’
          end SecurityIssue,
      c.wsec_cat_name,
      f.wsec_fea_act_name_id
  from
      dbo.msp_resources u
      inner join dbo.msp_web_security_sp_cat_relations r on u.res_security_guid = r.wsec_sp_guid
      left join dbo.msp_web_security_sp_cat_permissions p on r.wsec_rel_uid = p.wsec_rel_uid
      left join msp_web_security_categories c on r.wsec_cat_uid = c.wsec_cat_uid
      left join msp_web_security_features_actions f on p.wsec_fea_act_uid = f.wsec_fea_act_uid
  where
      p.wsec_allow = 1 or p.wsec_deny = 1 or c.wsec_cat_uid is not null
  order by

      u.res_name

MS Project Server 2007 Version

— This Query give gives the list of groups with associated users. (by FM)

select sg.Wsec_Grp_ID as GroupID, sg.Wsec_GRP_Name as GroupName, mr.Wres_ID,
mr.Res_name as ResourceName, mr.Wres_NT_Account as NTAccount, mr.Wres_email as Email,
mr.Wres_Use_NT_logon as UseWinAuthentication, mr.Wres_Is_Enabled as IsEnabled, mr.Wres_Can_Login as CanLogin,
mr.Wres_Last_Connect as LastConnect, mr.Wres_is_Manager as IsManager
from msp_web_security_groups sg
inner join msp_web_security_group_members sgm on sg.Wsec_grp_guid = sgm.Wsec_grp_guid
inner join msp_web_resources mr on sgm.Wres_GUID = mr.Wres_GUID
–where sg.wsec_grp_id = 4 AND sg.WSec_grp_name like ‘Project Managers’
order by sg.wsec_grp_name, mr.res_name



Contact your DBA / Project Admin to run this query. It is not recommended to run SQL queries again production Project Server / SharePoint databases.

Few external References with related Information
  1. Create a report of Users and Security Groups
 

Leave a Reply

Sign up for Softvative Newsletter

Get notified about new articles and deals

Receive the latest technology and leadership news and resources from us

Subscribe To Softvative Newsletter