There are several instances in PS2010 where the AD Resource Sync is partially failing and causing active resources to be deactivated in Project Server. One of the causes is mismatch of the AD GUID. This can happen either if the account was removed from AD then re-added, or if the PS account was created manually. The solution to this is simple. Using the SQL statements below, the mis-mapping on an account can be removed. Then the AD sync will repair the mis-match on the GUID when the next Synchronization occurs.
Using SQL Server Management studio, create a query against the Published Database for the affected PS instance.
-- Identify the entry for the affected user
SELECT RES_UID,RES_NAME, WRES_AD_GUID FROM MSP_RESOURCES
ORDER BY RES_NAME
-- Copy the RES_UID from the entry for use in the following queries in place of [RES_UID]
-- Modify the user entry to make the WRES_AD_GUID Null
WHERE RES_UID ='[RES_UID]'
--Verify the change
SELECT * FROM MSP_RESOURCES
When the next Active Directory Synchronization occurs, the WRES_AD_GUID should be re-populated with the correct value, and the user should appear active in Project Server.
This issue is "by-design" as reactivating an ADGUID could potentially open a security hole.
Take the following scenario:
You have an executive Bob Smith.His e-mail is Bob.Smith@XYZ.COM. Hisaccount name is Domain\BobS.
As an exec, he has broad accessto ALL project server workspace and projects.
He quits and leaves the company.His account is inactivated in PWA.
Company hires a new person, BobSmith, a temp worker. If Project Server can map his name, e-mail or accountname, and we DON’T have the AD Guid to differentiate the new bob from the old,that user could get mapped to the OLD bob smith account, and given his oldaccess rights across the entire project site.
Proper domain management usuallyinvolves never allowing this situation to arise at all. That means TombstoningBob Smith instead of deleting his account, and never re-using e-mail/accountnames at all.
Deleting the WRES_AD_GUID is ajudgment call on the customer’s part, and if they do it in the wrong instance,the wrong person could get very broad access to all Project Server/WSS data.
If the customer has a highturnover-return rate, they should definitely be using the tombstone method,because when the same physical person returns, they can re-animate thetombstoned AD account, instead of creating a new one. This is by far thepreferred method in Windows Server 2008 functional Active Directories and newer.
Allowing the users to getimproperly mapped could be a major security issue, and the ADGuid is thestrongest protection against that sort of accident.
Here’s some info for thetombstone/reanimate functionality.
Thanks to MCART for the additional research and explanation on this issue.