Query to List PWA Groups with Resources

Query to List PWA Groups with Resources

Version 3.2, Modified: 01/24/20175

Applies To: Project Server 2007, 2010, 2013


The following query gives you list of groups with users, their NT accounts, email address & some other details. 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 2013 Version

 

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

Enter the CAPTCHA * Time limit is exhausted. Please reload CAPTCHA.