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.
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.