Archive for February, 2006

How to Back and Restore Project DB

Friday, February 17th, 2006

Part2 Activate AD GUID for Windows Authenticated Resources in PS2003

Monday, February 6th, 2006

In part1 of this article I mentioned how I enabled the AD-GUID for a single resource. Now what if you have hundreds or thousands of resources. In that scenario it would be difficult to run those queries (in step 7 & 8) for each resource in question.

I created queries keeping in the mind the large number of resouces which needs to be updated.

Query1 a:
–Query to view Resources with missing AD GUID from msp_web_resources

select Res_name, WRes_NT_Account, WRes_Email, WRes_AD_GUID from msp_web_resources
where Wres_AD_GUID is null
AND Wres_Use_NT_Logon = 1
order by Res_Name

Query1 b:
–View / Update Resources with missing AD GUID which needs update in MSP_Resources

select mspr.Res_name, mspr.Res_AD_GUID
from msp_resources mspr, msp_Web_resources mspwr
where mspr.Res_AD_GUID is null AND mspr.Res_name = mspwr.Res_name
AND mspwr.Wres_Use_NT_Logon = 1
order by mspr.Res_Name

Query2 a:
–Update all records in MSP_Resources with updated Resource Name from MSP_Web_Resources

update msp_resources
set msp_Resources.Res_Name = msp_Web_Resources.Res_Name
from msp_Resources, msp_Web_Resources
where msp_Resources.Res_EUID = msp_Web_Resources.Res_EUID
AND msp_Resources.Res_AD_GUID is null
or msp_Resources.Res_AD_GUID = msp_Web_Resources.WRes_AD_GUID
AND Wres_Use_NT_Logon = 1

Query2 b:
–Update all resource name records in MSP_Resources with AD GUID from MSP_Web_Resources

update msp_resources
set msp_Resources.Res_AD_GUID = msp_Web_Resources.WRes_AD_GUID
from msp_Resources, msp_Web_Resources
where msp_Resources.Res_Name = msp_Web_Resources.Res_Name
AND msp_Resources.Res_AD_GUID is null
AND Wres_Use_NT_Logon = 1

(I don’t guarantee any issues which may arise from changes done by following this article. Test this process first in development. Contact your DBA before running queries. )