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

WITH(NOLOCK)

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

UPDATE MSP_RESOURCES

SET WRES_AD_GUID=NULL

WHERE RES_UID ='[RES_UID]'

 

--Verify the change

SELECT * FROM MSP_RESOURCES

WHERE RES_UID ='[RES_UID]'

 

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.

 

_______________________

Update:

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. His
account name is Domain\BobS.

As an exec, he has broad access
to ALL project server workspace and projects.

He quits and leaves the company.
His account is inactivated in PWA.

Company hires a new person, Bob
Smith, a temp worker. If Project Server can map his name, e-mail or account
name, 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 old
access rights across the entire project site.

 

Proper domain management usually
involves never allowing this situation to arise at all. That means Tombstoning
Bob Smith instead of deleting his account, and never re-using e-mail/account
names at all.

Deleting the WRES_AD_GUID is a
judgment 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 high
turnover-return rate, they should definitely be using the tombstone method,
because when the same physical person returns, they can re-animate the
tombstoned AD account, instead of creating a new one. This is by far the
preferred method in Windows Server 2008 functional Active Directories and newer.

 

Allowing the users to get
improperly mapped could be a major security issue, and the ADGuid is the
strongest protection against that sort of accident.

 _______________________

Here’s some info for the
tombstone/reanimate functionality.

http://technet.microsoft.com/en-us/magazine/2007.09.tombstones.aspx

________________________

Thanks to MCART for the additional research and explanation on this issue.