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