Part2 Activate AD GUID for Windows Authenticated Resources in PS2003

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. )

Leave a Reply

Enter the CAPTCHA * Time limit is exhausted. Please reload CAPTCHA.