Searching for Security Gaps

When I am confronted with the security setup for a new customer it is relatively easy to go through the definitions of the Groups and Categories. What is more difficult, is finding the users that have permissions or categories assigned directly to them. (See Do Not Assign Categories or Permissions Directly to Users to find out why you don't want this.)

When the built-in user interface doesn't answer your questions, consider going to the data.

  use [ProjectServer_Published]
  select
      u.res_name,
      case when c.wsec_cat_name is null
          then 'Permission assigned directly to user'
          else 'Category assigned directly 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
  

Now this SELECT statement needs to be run against the Published database which means we are in unsupported territory so this query could fail when you apply some future hotfix, service pack or upgrade to a future version.