If you use this for the workstation install, you can replace all of the various accounts (CatalogWebSvc, ProfilesWebSvc) with RuntimeUser or its equivalent.
Copy the script from below or down load it from here: CS2007SqlConfigurationScript.sql
/*****************************************************************************************
Setup Windows integrated database logins for all relevant windows accounts
INSTRUCTIONS:
1) Perform the following search and replace:
a) update the machine name by searching/replacing "<THE_SERVER_NAME>"
b) update the site name by searching/replacing "<Site_Name>"
2) If you installed the CS2007 staging component then uncomment the appropriate section toward the
end of the script. Otherwise those users/roles will not be setup.
Note that if you did not take the default naming convention for
the database tables you may have additional updates/corrections to make.
*****************************************************************************************/
use MSCS_Admin
go
sp_grantlogin '<THE_SERVER_NAME>\ASPNET'
go
sp_grantlogin '<THE_SERVER_NAME>\RunTimeUser'
go
sp_grantlogin '<THE_SERVER_NAME>\CatalogWebSvc'
go
--sp_grantlogin '<THE_SERVER_NAME>\CSDMSvc'
--go
--sp_grantlogin '<THE_SERVER_NAME>\CSStageSvc'
--go
--sp_grantlogin '<THE_SERVER_NAME>\CSSUser (Commerce Server Staging User)
--go
sp_grantlogin '<THE_SERVER_NAME>\MarketingWebSvc'
go
sp_grantlogin '<THE_SERVER_NAME>\OrdersWebSvc'
go
sp_grantlogin '<THE_SERVER_NAME>\ProfilesWebSvc'
go
--*****************************************************************************************
-- Grant database access and roles for each login
--*****************************************************************************************
-- **** database account ASPNET ****
use MSCS_Admin
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\ASPNET'
go
sp_addrolemember 'db_datareader', '<THE_SERVER_NAME>\ASPNET'
go
-- **** database account CatalogWebSvc ****
use MSCS_Admin
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\CatalogWebSvc'
go
sp_addrolemember 'admin_reader_role', '<THE_SERVER_NAME>\CatalogWebSvc'
go
use MSCS_CatalogScratch
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\CatalogWebSvc'
go
sp_addrolemember 'db_ddladmin', '<THE_SERVER_NAME>\CatalogWebSvc'
go
sp_addrolemember 'db_datareader', '<THE_SERVER_NAME>\CatalogWebSvc'
go
sp_addrolemember 'db_datawriter', '<THE_SERVER_NAME>\CatalogWebSvc'
go
use <Site_Name>_ProductCatalog
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\CatalogWebSvc'
go
sp_addrolemember 'ctlg_CatalogWriterRole', '<THE_SERVER_NAME>\CatalogWebSvc'
go
sp_addrolemember 'db_ddladmin', '<THE_SERVER_NAME>\CatalogWebSvc'
go
sp_addrolemember 'db_securityadmin', '<THE_SERVER_NAME>\CatalogWebSvc'
go
sp_addrolemember 'Inventory_ReaderRole', '<THE_SERVER_NAME>\CatalogWebSvc'
go
sp_addrolemember 'Inventory_WriterRole', '<THE_SERVER_NAME>\CatalogWebSvc'
go
sp_addrolemember 'db_datareader', '<THE_SERVER_NAME>\CatalogWebSvc'
go
sp_addrolemember 'db_datawriter', '<THE_SERVER_NAME>\CatalogWebSvc'
go
-- **** database account MarketingWebSvc ****
use MSCS_Admin
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\MarketingWebSvc'
go
sp_addrolemember 'admin_reader_role', '<THE_SERVER_NAME>\MarketingWebSvc'
go
use <Site_Name>_Marketing
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\MarketingWebSvc'
go
sp_addrolemember 'mktg_MarketingService_role', '<THE_SERVER_NAME>\MarketingWebSvc'
go
sp_addrolemember 'mktg_runtime_role', '<THE_SERVER_NAME>\MarketingWebSvc'
go
sp_addrolemember 'mktg_dataManager_role', '<THE_SERVER_NAME>\MarketingWebSvc'
go
sp_addrolemember 'mktg_promoCodeGenerator_role', '<THE_SERVER_NAME>\MarketingWebSvc'
go
use <Site_Name>_marketing_lists
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\MarketingWebSvc'
go
sp_addrolemember 'db_owner', '<THE_SERVER_NAME>\MarketingWebSvc'
go
use <Site_Name>_ProductCatalog
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\MarketingWebSvc'
go
sp_addrolemember 'ctlg_catalogReaderRole', '<THE_SERVER_NAME>\MarketingWebSvc'
go
use <Site_Name>_Profiles
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\MarketingWebSvc'
go
sp_addrolemember 'Profile_Reader', '<THE_SERVER_NAME>\MarketingWebSvc'
go
sp_addrolemember 'Profile_Schema_Reader', '<THE_SERVER_NAME>\MarketingWebSvc'
go
-- **** database account OrdersWebSvc ****
use MSCS_Admin
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\OrdersWebSvc'
go
sp_addrolemember 'admin_reader_role', '<THE_SERVER_NAME>\OrdersWebSvc'
go
use MSCS_CatalogScratch
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\OrdersWebSvc'
go
sp_addrolemember 'db_datareader', '<THE_SERVER_NAME>\OrdersWebSvc'
go
sp_addrolemember 'db_datawriter', '<THE_SERVER_NAME>\OrdersWebSvc'
go
sp_addrolemember 'db_ddladmin', '<THE_SERVER_NAME>\OrdersWebSvc'
go
use <Site_Name>_Marketing
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\OrdersWebSvc'
go
sp_addrolemember 'db_ddladmin', '<THE_SERVER_NAME>\OrdersWebSvc'
go
sp_addrolemember 'mktg_runtime_role', '<THE_SERVER_NAME>\OrdersWebSvc'
go
use <Site_Name>_ProductCatalog
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\OrdersWebSvc'
go
sp_addrolemember 'ctlg_catalogReaderRole', '<THE_SERVER_NAME>\OrdersWebSvc'
go
sp_addrolemember 'Inventory_ReaderRole', '<THE_SERVER_NAME>\OrdersWebSvc'
go
use <Site_Name>_Profiles
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\OrdersWebSvc'
go
sp_addrolemember 'Profile_Reader', '<THE_SERVER_NAME>\OrdersWebSvc'
go
sp_addrolemember 'Profile_Schema_Reader', '<THE_SERVER_NAME>\OrdersWebSvc'
go
use <Site_Name>_TransactionConfig
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\OrdersWebSvc'
go
sp_addrolemember 'Orders_Management', '<THE_SERVER_NAME>\OrdersWebSvc'
go
use <Site_Name>_Transactions
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\OrdersWebSvc'
go
sp_addrolemember 'Orders_Management', '<THE_SERVER_NAME>\OrdersWebSvc'
go
sp_addrolemember 'Orders_Runtime', '<THE_SERVER_NAME>\OrdersWebSvc'
go
-- **** database account ProfilesWebSvc ****
use MSCS_Admin
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\ProfilesWebSvc'
go
sp_addrolemember 'admin_reader_role', '<THE_SERVER_NAME>\ProfilesWebSvc'
go
use <Site_Name>_Profiles
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\ProfilesWebSvc'
go
sp_addrolemember 'Profile_Schema_Manager', '<THE_SERVER_NAME>\ProfilesWebSvc'
go
sp_addrolemember 'Profile_Runtime', '<THE_SERVER_NAME>\ProfilesWebSvc'
go
-- **** database account RunTimeUser ****
use MSCS_Admin
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\RunTimeUser'
go
sp_addrolemember 'admin_reader_role', '<THE_SERVER_NAME>\RunTimeUser'
go
use MSCS_CatalogScratch
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\RunTimeUser'
go
sp_addrolemember 'db_datareader', '<THE_SERVER_NAME>\RunTimeUser'
go
sp_addrolemember 'db_datawriter', '<THE_SERVER_NAME>\RunTimeUser'
go
sp_addrolemember 'db_ddladmin', '<THE_SERVER_NAME>\RunTimeUser'
go
use <Site_Name>_Marketing
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\RunTimeUser'
go
sp_addrolemember 'db_ddladmin', '<THE_SERVER_NAME>\RunTimeUser'
go
sp_addrolemember 'mktg_runtime_role', '<THE_SERVER_NAME>\RunTimeUser'
go
use <Site_Name>_Marketing_lists
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\RunTimeUser'
go
sp_addrolemember 'db_datareader', '<THE_SERVER_NAME>\RunTimeUser'
go
use <Site_Name>_ProductCatalog
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\RunTimeUser'
go
sp_addrolemember 'ctlg_catalogReaderRole', '<THE_SERVER_NAME>\RunTimeUser'
go
sp_addrolemember 'Inventory_RuntimeRole', '<THE_SERVER_NAME>\RunTimeUser'
go
use <Site_Name>_Profiles
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\RunTimeUser'
go
sp_addrolemember 'Profile_Schema_Reader', '<THE_SERVER_NAME>\RunTimeUser'
go
sp_addrolemember 'Profile_Runtime', '<THE_SERVER_NAME>\RunTimeUser'
use <Site_Name>_TransactionConfig
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\RunTimeUser'
go
sp_addrolemember 'Orders_Runtime', '<THE_SERVER_NAME>\RunTimeUser'
go
use <Site_Name>_Transactions
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\RunTimeUser'
go
sp_addrolemember 'Orders_Runtime', '<THE_SERVER_NAME>\RunTimeUser'
go
/*
-- **** database account CSDMSvc ****
use DirectMailer
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\CSDMSvc'
go
sp_addrolemember 'db_owner', '<THE_SERVER_NAME>\CSDMSvc'
go
use MSCS_Admin
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\CSDMSvc'
go
sp_addrolemember 'admin_reader_role', '<THE_SERVER_NAME>\CSDMSvc'
use <Site_Name>_Marketing
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\CSDMSvc'
go
sp_addrolemember 'mktg_directmailer_role', '<THE_SERVER_NAME>\CSDMSvc'
use <Site_Name>_Marketing_lists
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\CSDMSvc'
go
sp_addrolemember 'db_owner', '<THE_SERVER_NAME>\CSDMSvc'
use <Site_Name>_Profiles
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\CSDMSvc'
go
sp_addrolemember 'Profile_Schema_Reader', '<THE_SERVER_NAME>\CSDMSvc'
go
sp_addrolemember 'Profile_Reader', '<THE_SERVER_NAME>\CSDMSvc'
go
-- **** database account CSStageSvc ****
use MSCS_Admin
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\CSStageSvc'
go
sp_addrolemember 'admin_reader_role', '<THE_SERVER_NAME>\CSStageSvc'
go
use MSCS_CatalogScratch
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\CSStageSvc'
go
sp_addrolemember 'db_datareader', '<THE_SERVER_NAME>\CSStageSvc'
go
sp_addrolemember 'db_datawriter', '<THE_SERVER_NAME>\CSStageSvc'
go
sp_addrolemember 'db_ddladmin', '<THE_SERVER_NAME>\CSStageSvc'
go
use <Site_Name>_Marketing
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\CSStageSvc'
go
sp_addrolemember 'db_ddladmin', '<THE_SERVER_NAME>\CSStageSvc'
go
sp_addrolemember 'mktg_staging_role', '<THE_SERVER_NAME>\CSStageSvc'
go
use <Site_Name>_Marketing_lists
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\CSStageSvc'
go
sp_addrolemember 'godb_datareader', '<THE_SERVER_NAME>\CSStageSvc'
go
use <Site_Name>_ProductCatalog
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\CSStageSvc'
go
sp_addrolemember 'ctlg_CatalogWriterRole', '<THE_SERVER_NAME>\CSStageSvc'
go
sp_addrolemember 'db_datareader', '<THE_SERVER_NAME>\CSStageSvc'
go
sp_addrolemember 'db_datawriter', '<THE_SERVER_NAME>\CSStageSvc'
go
sp_addrolemember 'db_ddladmin', '<THE_SERVER_NAME>\CSStageSvc'
go
sp_addrolemember 'db_securityadmin', '<THE_SERVER_NAME>\CSStageSvc'
go
sp_addrolemember 'Inventory_ReaderRole', '<THE_SERVER_NAME>\CSStageSvc'
go
sp_addrolemember 'Inventory_WriterRole', '<THE_SERVER_NAME>\CSStageSvc'
go
use <Site_Name>_Profiles
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\CSStageSvc'
go
sp_addrolemember 'Profile_Schema_Manager', '<THE_SERVER_NAME>\CSStageSvc'
go
use <Site_Name>_TransactionConfig
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\CSStageSvc'
go
sp_addrolemember 'Orders_Management', '<THE_SERVER_NAME>\CSStageSvc'
go
-- **** database account CSSUser (<Domain>\<Staging User>) ****
use <Site_Name>_ProductCatalog
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\CSSUser'
go
sp_addrolemember 'ctlg_CatalogReaderRole', '<THE_SERVER_NAME>\CSSUser'
go
sp_addrolemember 'Inventory_ReaderRole', '<THE_SERVER_NAME>\CSSUser'
go
use MSCS_Admin
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\CSSUser'
go
sp_addrolemember 'db_datareader', '<THE_SERVER_NAME>\CSSUser'
go
use MSCS_CatalogScratch
go
sp_grantdbaccess @loginame = '<THE_SERVER_NAME>\CSSUser'
go
sp_addrolemember 'db_datareader', '<THE_SERVER_NAME>\CSSUser'
go
sp_addrolemember 'db_datawriter', '<THE_SERVER_NAME>\CSSUser'
go
sp_addrolemember 'db_ddladmin', '<THE_SERVER_NAME>\CSSUser'
go
*/