Resource Permission Queries in Project Server
By Collin Quiring
This post is about Project Server 2010 (although this probably works with Project Server 2007) and it might work with Project Server 2013 but I haven’t had time to test that yet (on premises version of 2013). I have recently been working on finding the permissions of specific User (in my case, all Users are also Resources) in Project Web APP (PWA) based upon which Group in which they are a member. There is the View Effective Permissions tool which was built for 2007 but in my experience also works in 2010. However, that tool is too limited for my needs.
I am trying to find out exactly what permissions are allowed/denied (or neither) and roll that from Resource to Group. I have found queries on TechNet and other blogs which come close but most of them seem to concentrate on what is Denied to the user directly, rather than Allowed, and rather than based on Group.
To clarify what I am referring to in Denied and Allowed, there are really four options in the Allow and Deny checkboxes. You can either check them (explicitly denying or allowing something) or leave them blank. Which, from a database perspective is different. As an example, if there is a specific permission that has neither Allow nor Deny checked then in the database they are both False. The database for the query is True/False (on/off) result. So, you need to have both in the query to show you the potential combinations of True or False. If you just look at what is Denied, that doesn’t show you what is Allowed and in my case I am trying to determine what permissions the Resource does have – which means they may not have Denied checked, but they may not have Allowed checked either and that affects their permissions. Just a quick tangent there….
NOTE – it is not supported to use the Published database for reporting but in this case the information is not found in the Reporting database.
Also, since I have some different queries that got me to the final version that I have included here. These are to give me different bits of information based on the data requirement. I share this only as they might be of value to somebody else and I always appreciate it when another person has posted their queries online.
As a reminder, the best way to use the security settings from Project Server is to NOT give direct permissions to a User (Resource in my case). However, there are some situations where that is a necessity (or at least extremely convenient) for an individual or two. But, since Administrators don’t always follow a prescribed change control process and may not always remember every change they make it is nice to have a query to find those Users that have been given permissions directly.
Here is that query.
Users with direct permissions:
SELECT MSP_RESOURCES.RES_NAME AS Resourcename, CONV.CONV_STRING AS Permission, MSP_WEB_SECURITY_SP_CAT_PERMISSIONS.WSEC_DENY AS Denied,
MSP_WEB_SECURITY_SP_CAT_PERMISSIONS.WSEC_ALLOW AS Allowed
FROM MSP_WEB_SECURITY_SP_CAT_PERMISSIONS INNER JOIN
MSP_WEB_SECURITY_SP_CAT_RELATIONS ON
MSP_WEB_SECURITY_SP_CAT_PERMISSIONS.WSEC_REL_UID = MSP_WEB_SECURITY_SP_CAT_RELATIONS.WSEC_REL_UID INNER JOIN
MSP_WEB_SECURITY_FEATURES_ACTIONS ON
MSP_WEB_SECURITY_FEATURES_ACTIONS.WSEC_FEA_ACT_UID = MSP_WEB_SECURITY_SP_CAT_PERMISSIONS.WSEC_FEA_ACT_UID INNER JOIN
MSP_WEB_CONVERSIONS AS CONV ON CONV.CONV_VALUE = MSP_WEB_SECURITY_FEATURES_ACTIONS.WSEC_FEA_ACT_NAME_ID INNER JOIN
MSP_RESOURCES ON MSP_WEB_SECURITY_SP_CAT_RELATIONS.WSEC_SP_GUID = MSP_RESOURCES.RES_SECURITY_GUID
WHERE (CONV.LANG_ID = 1033)
ORDER BY Resourcename, Permission
And, a sample of what this looks like when run in SSRS (SQL Server Reporting Services):
The next report that I have is to get the Group permissions – without the Resource in this example.
Group security:
SELECT Distinct WSG.WSEC_GRP_NAME AS [Group], CONV.CONV_STRING AS Permission, WCP.WSEC_DENY AS Denied, WCP.WSEC_ALLOW AS Allowed
FROM MSP_WEB_SECURITY_GROUPS AS WSG INNER JOIN
MSP_WEB_SECURITY_SP_CAT_RELATIONS AS WSCR ON WSG.WSEC_GRP_GUID = WSCR.WSEC_SP_GUID INNER JOIN
MSP_WEB_SECURITY_SP_CAT_PERMISSIONS AS WCP ON WCP.WSEC_REL_UID = WSCR.WSEC_REL_UID INNER JOIN
MSP_WEB_SECURITY_FEATURES_ACTIONS AS SFA ON SFA.WSEC_FEA_ACT_UID = WCP.WSEC_FEA_ACT_UID INNER JOIN
MSP_WEB_CONVERSIONS AS CONV ON CONV.CONV_VALUE = SFA.WSEC_FEA_ACT_NAME_ID
WHERE (CONV.LANG_ID = 1033)
ORDER BY [Group], Permission
And, a sample of what this looks like when run in SSRS:
Another query, without permissions, is to determine which users are in which groups.
Users in which groups:
SELECT MSP_RESOURCES.RES_NAME, MSP_WEB_SECURITY_GROUPS.WSEC_GRP_NAME AS [Group Name]
FROM MSP_WEB_SECURITY_GROUP_MEMBERS INNER JOIN
MSP_WEB_SECURITY_GROUPS ON MSP_WEB_SECURITY_GROUP_MEMBERS.WSEC_GRP_GUID = MSP_WEB_SECURITY_GROUPS.WSEC_GRP_GUID INNER JOIN
MSP_RESOURCES ON MSP_WEB_SECURITY_GROUP_MEMBERS.WRES_GUID = MSP_RESOURCES.RES_SECURITY_GUID
WHERE (MSP_RESOURCES.RES_SECURITY_GUID IN
(SELECT WRES_GUID
FROM MSP_WEB_SECURITY_GROUP_MEMBERS AS MSP_WEB_SECURITY_GROUP_MEMBERS_1))
And, a sample of what this looks like when run in SSRS:
And, finally, the report that I talked about at the beginning of this post – the permissions for the Resource in the Group and the permissions for each Resource. In my case, it made more sense to sort by Group first but I think most others would prefer to sort by Resource and then Group and then Permission.
Resource in Group Permission:
SELECT WSG.WSEC_GRP_NAME AS [Group], CONV.CONV_STRING AS Permission, WCP.WSEC_DENY AS Denied, WCP.WSEC_ALLOW AS Allowed,
MSP_RESOURCES.RES_NAME
FROM MSP_WEB_SECURITY_GROUPS AS WSG INNER JOIN
MSP_WEB_SECURITY_SP_CAT_RELATIONS AS WSCR ON WSG.WSEC_GRP_GUID = WSCR.WSEC_SP_GUID INNER JOIN
MSP_WEB_SECURITY_SP_CAT_PERMISSIONS AS WCP ON WCP.WSEC_REL_UID = WSCR.WSEC_REL_UID INNER JOIN
MSP_WEB_SECURITY_FEATURES_ACTIONS AS SFA ON SFA.WSEC_FEA_ACT_UID = WCP.WSEC_FEA_ACT_UID INNER JOIN
MSP_WEB_CONVERSIONS AS CONV ON CONV.CONV_VALUE = SFA.WSEC_FEA_ACT_NAME_ID INNER JOIN
MSP_WEB_SECURITY_GROUP_MEMBERS ON WSG.WSEC_GRP_GUID = MSP_WEB_SECURITY_GROUP_MEMBERS.WSEC_GRP_GUID INNER JOIN
MSP_RESOURCES ON MSP_WEB_SECURITY_GROUP_MEMBERS.WRES_GUID = MSP_RESOURCES.RES_SECURITY_GUID
WHERE (CONV.LANG_ID = 1033)
ORDER BY [Group], Permission
And, a sample of what this looks like when run in SSRS:
Comments
Leave a Reply
You must be logged in to post a comment.