MyCommerceServerUtilities

Microsoft Commerce Server 2007 setup is a bit tedious. The install itself isn't bad but the configuration has numerous steps and is error prone. I'm beginning to put together some tools to help alleviate this.

SQL Scripts

This is the beginning of a script that takes care of the creation of SQL logins, accounts and roles configuration.

/*****************************************************************************************

        Setup Windows integrated database logins for all relevent 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

*/

CategoryMicrosoftCommerceServer