mikedopp.com Code Monkey by Day, Unix/Windows Administrator by Night, Social Media Chump 24/7

Commerce Server 2007 Scripts to make your Commerce Server 2007 Life easier

Before I start I have to give a huge hat tip to Wade Wegner for these scripts as they are not mine. I am simply just helping to make them more accessible.

Truth be known these scripts should be included in every install of Commerce Server 2007.

Create CS 2007 Local Users VB Script:

' Set the local computer name
strComputer = "."

' Run the Load method
Load

' Encapsulates the processing of this script
Sub Load()

    ' Create the CS 2007 users
    CreateUser "CatalogWebSvc","Pa$$w0rd","Account for running the Catalog Web service"
    CreateUser "CSDNSvc","Pa$$w0rd","Account for running the Commerce Server Direct mailer service"
    CreateUser "CSHealthMonitorSvc","Pa$$w0rd","Account for running the Commerce Server health Monitoring service"
    CreateUser "CSLOB","Pa$$w0rd","Account for running the Commerce Server adapters"
    CreateUser "CSStageSvc","Pa$$w0rd","Account for running the Commerce Server Staging service"
    CreateUser "MarketingWebSvc","Pa$$w0rd","Account for running the Marketing Web service"
    CreateUser "OrdersWebSvc","Pa$$w0rd","Account for running the Orders Web service"
    CreateUser "ProfilesWebSvc","Pa$$w0rd","Account for running the Profiles Web service"
    CreateUser "RunTimeUser","Pa$$w0rd","IIS account for accessing a Commerce Server site or application"

    MsgBox "Complete!"

End Sub

' Create the local user
Sub CreateUser(userName, password, description)

    ' Check to see if the user exists; if so, then skip
    If NOT CheckIfUserExists(userName) Then
        Set objComputer = GetObject("WinNT://" & strComputer & "")
        Set objUser = objComputer.Create("user", userName)

        objUser.SetPassword password
        objUser.FullName = userName
        objUser.Description = description
        objUser.Put "UserFlags", 65600 ' Sets Password Never Expires to TRUE
                                       ' and sets User Can't Change Password to TRUE
        objUser.SetInfo
    Else
        MsgBox userName & " already exists!"
    End If

End Sub

' Check to see if user exists
Function CheckIfUserExists(userName)

    Set objComputer = GetObject("WinNT://" & strComputer & "")
    objComputer.Filter = Array("user")
    intFound = 0

    For Each User In objComputer
        If lcase(User.Name) = lcase(userName) Then
            intFound = 1
        End If
    Next

    If intFound = 1 Then
        CheckIfUserExists = True
    Else
        CheckIfUserExists = False
    End If

End Function

Create CS Logins and Assign Roles SQL Script:

Note:

I would recommend opening this in Notepad (or your favorite editor) and replace all the CSharpSite with your Website name.

As well as CS2007 with your Server HOST Name.

USE [master]
GO
CREATE LOGIN [CS2007\ASPNET] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
CREATE LOGIN [CS2007\CatalogWebSvc] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
CREATE LOGIN [CS2007\CSDMSvc] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
CREATE LOGIN [CS2007\CSHealthMonitorSvc] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
CREATE LOGIN [CS2007\CSStageSvc] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
CREATE LOGIN [CS2007\MarketingWebSvc] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
CREATE LOGIN [CS2007\OrdersWebSvc] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
CREATE LOGIN [CS2007\ProfilesWebSvc] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
CREATE LOGIN [CS2007\RunTimeUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

----------------------------------------------------------------
-- Assign database accounts to database roles in SQL Server 2005
---------------------------------------------------------------- 

-- ASPNET
USE [MSCS_Admin]
GO
CREATE USER [CS2007\ASPNET] FOR LOGIN [CS2007\ASPNET]
GO
USE [MSCS_Admin]
GO
EXEC sp_addrolemember N'db_datareader', N'CS2007\ASPNET'
GO

--CatalogWebSvc
USE [CSharpSite_productcatalog]
GO
CREATE USER [CS2007\CatalogWebSvc] FOR LOGIN [CS2007\CatalogWebSvc]
GO
USE [CSharpSite_productcatalog]
GO
EXEC sp_addrolemember N'ctlg_CatalogWriterRole', N'CS2007\CatalogWebSvc'
GO
USE [CSharpSite_productcatalog]
GO
EXEC sp_addrolemember N'db_datareader', N'CS2007\CatalogWebSvc'
GO
USE [CSharpSite_productcatalog]
GO
EXEC sp_addrolemember N'db_datawriter', N'CS2007\CatalogWebSvc'
GO
USE [CSharpSite_productcatalog]
GO
EXEC sp_addrolemember N'db_ddladmin', N'CS2007\CatalogWebSvc'
GO
USE [CSharpSite_productcatalog]
GO
EXEC sp_addrolemember N'db_securityadmin', N'CS2007\CatalogWebSvc'
GO
USE [CSharpSite_productcatalog]
GO
EXEC sp_addrolemember N'Inventory_ReaderRole', N'CS2007\CatalogWebSvc'
GO
USE [CSharpSite_productcatalog]
GO
EXEC sp_addrolemember N'Inventory_WriterRole', N'CS2007\CatalogWebSvc'
GO
USE [MSCS_Admin]
GO
CREATE USER [CS2007\CatalogWebSvc] FOR LOGIN [CS2007\CatalogWebSvc]
GO
USE [MSCS_Admin]
GO
EXEC sp_addrolemember N'admin_reader_role', N'CS2007\CatalogWebSvc'
GO
USE [MSCS_CatalogScratch]
GO
CREATE USER [CS2007\CatalogWebSvc] FOR LOGIN [CS2007\CatalogWebSvc]
GO
USE [MSCS_CatalogScratch]
GO
EXEC sp_addrolemember N'db_datareader', N'CS2007\CatalogWebSvc'
GO
USE [MSCS_CatalogScratch]
GO
EXEC sp_addrolemember N'db_datawriter', N'CS2007\CatalogWebSvc'
GO
USE [MSCS_CatalogScratch]
GO
EXEC sp_addrolemember N'db_ddladmin', N'CS2007\CatalogWebSvc'
GO

-- MarketingWebSvc
USE [CSharpSite_marketing]
GO
CREATE USER [CS2007\MarketingWebSvc] FOR LOGIN [CS2007\MarketingWebSvc]
GO
USE [CSharpSite_marketing]
GO
EXEC sp_addrolemember N'mktg_marketingService_role', N'CS2007\MarketingWebSvc'
GO
USE [CSharpSite_marketing]
GO
EXEC sp_addrolemember N'mktg_promoCodeGenerator_role', N'CS2007\MarketingWebSvc'
GO
USE [CSharpSite_marketing_lists]
GO
CREATE USER [CS2007\MarketingWebSvc] FOR LOGIN [CS2007\MarketingWebSvc]
GO
USE [CSharpSite_marketing_lists]
GO
EXEC sp_addrolemember N'db_owner', N'CS2007\MarketingWebSvc'
GO
USE [CSharpSite_productcatalog]
GO
CREATE USER [CS2007\MarketingWebSvc] FOR LOGIN [CS2007\MarketingWebSvc]
GO
USE [CSharpSite_productcatalog]
GO
EXEC sp_addrolemember N'ctlg_CatalogReaderRole', N'CS2007\MarketingWebSvc'
GO
USE [CSharpSite_profiles]
GO
CREATE USER [CS2007\MarketingWebSvc] FOR LOGIN [CS2007\MarketingWebSvc]
GO
USE [CSharpSite_profiles]
GO
EXEC sp_addrolemember N'Profile_Reader', N'CS2007\MarketingWebSvc'
GO
USE [CSharpSite_profiles]
GO
EXEC sp_addrolemember N'Profile_Schema_Reader', N'CS2007\MarketingWebSvc'
GO
USE [MSCS_Admin]
GO
CREATE USER [CS2007\MarketingWebSvc] FOR LOGIN [CS2007\MarketingWebSvc]
GO
USE [MSCS_Admin]
GO
EXEC sp_addrolemember N'admin_reader_role', N'CS2007\MarketingWebSvc'
GO

-- OrdersWebSvc
USE [CSharpSite_marketing]
GO
CREATE USER [CS2007\OrdersWebSvc] FOR LOGIN [CS2007\OrdersWebSvc]
GO
USE [CSharpSite_marketing]
GO
EXEC sp_addrolemember N'db_ddladmin', N'CS2007\OrdersWebSvc'
GO
USE [CSharpSite_marketing]
GO
EXEC sp_addrolemember N'mktg_runtime_role', N'CS2007\OrdersWebSvc'
GO
USE [CSharpSite_productcatalog]
GO
CREATE USER [CS2007\OrdersWebSvc] FOR LOGIN [CS2007\OrdersWebSvc]
GO
USE [CSharpSite_productcatalog]
GO
EXEC sp_addrolemember N'ctlg_CatalogReaderRole', N'CS2007\OrdersWebSvc'
GO
USE [CSharpSite_productcatalog]
GO
EXEC sp_addrolemember N'Inventory_ReaderRole', N'CS2007\OrdersWebSvc'
GO
USE [CSharpSite_profiles]
GO
CREATE USER [CS2007\OrdersWebSvc] FOR LOGIN [CS2007\OrdersWebSvc]
GO
USE [CSharpSite_profiles]
GO
EXEC sp_addrolemember N'Profile_Reader', N'CS2007\OrdersWebSvc'
GO
USE [CSharpSite_profiles]
GO
EXEC sp_addrolemember N'Profile_Schema_Reader', N'CS2007\OrdersWebSvc'
GO
USE [CSharpSite_transactionconfig]
GO
CREATE USER [CS2007\OrdersWebSvc] FOR LOGIN [CS2007\OrdersWebSvc]
GO
USE [CSharpSite_transactionconfig]
GO
EXEC sp_addrolemember N'Orders_Management', N'CS2007\OrdersWebSvc'
GO
USE [CSharpSite_transactions]
GO
CREATE USER [CS2007\OrdersWebSvc] FOR LOGIN [CS2007\OrdersWebSvc]
GO
USE [CSharpSite_transactions]
GO
EXEC sp_addrolemember N'Orders_Management', N'CS2007\OrdersWebSvc'
GO
USE [CSharpSite_transactions]
GO
EXEC sp_addrolemember N'Orders_Runtime', N'CS2007\OrdersWebSvc'
GO
USE [MSCS_Admin]
GO
CREATE USER [CS2007\OrdersWebSvc] FOR LOGIN [CS2007\OrdersWebSvc]
GO
USE [MSCS_Admin]
GO
EXEC sp_addrolemember N'admin_reader_role', N'CS2007\OrdersWebSvc'
GO
USE [MSCS_CatalogScratch]
GO
CREATE USER [CS2007\OrdersWebSvc] FOR LOGIN [CS2007\OrdersWebSvc]
GO
USE [MSCS_CatalogScratch]
GO
EXEC sp_addrolemember N'db_datareader', N'CS2007\OrdersWebSvc'
GO
USE [MSCS_CatalogScratch]
GO
EXEC sp_addrolemember N'db_datawriter', N'CS2007\OrdersWebSvc'
GO
USE [MSCS_CatalogScratch]
GO
EXEC sp_addrolemember N'db_ddladmin', N'CS2007\OrdersWebSvc'
GO

-- ProfilesWebSvc
USE [CSharpSite_profiles]
GO
CREATE USER [CS2007\ProfilesWebSvc] FOR LOGIN [CS2007\ProfilesWebSvc]
GO
USE [CSharpSite_profiles]
GO
EXEC sp_addrolemember N'Profile_Runtime', N'CS2007\ProfilesWebSvc'
GO
USE [CSharpSite_profiles]
GO
EXEC sp_addrolemember N'Profile_Schema_Manager', N'CS2007\ProfilesWebSvc'
GO
USE [MSCS_Admin]
GO
CREATE USER [CS2007\ProfilesWebSvc] FOR LOGIN [CS2007\ProfilesWebSvc]
GO
USE [MSCS_Admin]
GO
EXEC sp_addrolemember N'admin_reader_role', N'CS2007\ProfilesWebSvc'
GO

-- RunTimeUser
USE [CSharpSite_marketing]
GO
CREATE USER [CS2007\RunTimeUser] FOR LOGIN [CS2007\RunTimeUser]
GO
USE [CSharpSite_marketing]
GO
EXEC sp_addrolemember N'db_ddladmin', N'CS2007\RunTimeUser'
GO
USE [CSharpSite_marketing]
GO
EXEC sp_addrolemember N'mktg_runtime_role', N'CS2007\RunTimeUser'
GO
USE [CSharpSite_marketing_lists]
GO
CREATE USER [CS2007\RunTimeUser] FOR LOGIN [CS2007\RunTimeUser]
GO
USE [CSharpSite_marketing_lists]
GO
EXEC sp_addrolemember N'db_datareader', N'CS2007\RunTimeUser'
GO
USE [CSharpSite_productcatalog]
GO
CREATE USER [CS2007\RunTimeUser] FOR LOGIN [CS2007\RunTimeUser]
GO
USE [CSharpSite_productcatalog]
GO
EXEC sp_addrolemember N'ctlg_CatalogReaderRole', N'CS2007\RunTimeUser'
GO
USE [CSharpSite_productcatalog]
GO
EXEC sp_addrolemember N'Inventory_RuntimeRole', N'CS2007\RunTimeUser'
GO
USE [CSharpSite_profiles]
GO
CREATE USER [CS2007\RunTimeUser] FOR LOGIN [CS2007\RunTimeUser]
GO
USE [CSharpSite_profiles]
GO
EXEC sp_addrolemember N'Profile_Runtime', N'CS2007\RunTimeUser'
GO
USE [CSharpSite_profiles]
GO
EXEC sp_addrolemember N'Profile_Schema_Reader', N'CS2007\RunTimeUser'
GO
USE [CSharpSite_transactionconfig]
GO
CREATE USER [CS2007\RunTimeUser] FOR LOGIN [CS2007\RunTimeUser]
GO
USE [CSharpSite_transactionconfig]
GO
EXEC sp_addrolemember N'Orders_Runtime', N'CS2007\RunTimeUser'
GO
USE [CSharpSite_transactions]
GO
CREATE USER [CS2007\RunTimeUser] FOR LOGIN [CS2007\RunTimeUser]
GO
USE [CSharpSite_transactions]
GO
EXEC sp_addrolemember N'Orders_Runtime', N'CS2007\RunTimeUser'
GO
USE [MSCS_Admin]
GO
CREATE USER [CS2007\RunTimeUser] FOR LOGIN [CS2007\RunTimeUser]
GO
USE [MSCS_Admin]
GO
EXEC sp_addrolemember N'admin_reader_role', N'CS2007\RunTimeUser'
GO
USE [MSCS_CatalogScratch]
GO
CREATE USER [CS2007\RunTimeUser] FOR LOGIN [CS2007\RunTimeUser]
GO
USE [MSCS_CatalogScratch]
GO
EXEC sp_addrolemember N'db_datareader', N'CS2007\RunTimeUser'
GO
USE [MSCS_CatalogScratch]
GO
EXEC sp_addrolemember N'db_datawriter', N'CS2007\RunTimeUser'
GO
USE [MSCS_CatalogScratch]
GO
EXEC sp_addrolemember N'db_ddladmin', N'CS2007\RunTimeUser'
GO

-- CSDMSvc
USE [CSharpSite_marketing]
GO
CREATE USER [CS2007\CSDMSvc] FOR LOGIN [CS2007\CSDMSvc]
GO
USE [CSharpSite_marketing]
GO
EXEC sp_addrolemember N'mktg_directmailer_role', N'CS2007\CSDMSvc'
GO
USE [CSharpSite_marketing_lists]
GO
CREATE USER [CS2007\CSDMSvc] FOR LOGIN [CS2007\CSDMSvc]
GO
USE [CSharpSite_marketing_lists]
GO
EXEC sp_addrolemember N'db_owner', N'CS2007\CSDMSvc'
GO
USE [CSharpSite_profiles]
GO
CREATE USER [CS2007\CSDMSvc] FOR LOGIN [CS2007\CSDMSvc]
GO
USE [CSharpSite_profiles]
GO
EXEC sp_addrolemember N'Profile_Reader', N'CS2007\CSDMSvc'
GO
USE [CSharpSite_profiles]
GO
EXEC sp_addrolemember N'Profile_Schema_Reader', N'CS2007\CSDMSvc'
GO
USE [DirectMailer]
GO
CREATE USER [CS2007\CSDMSvc] FOR LOGIN [CS2007\CSDMSvc]
GO
USE [DirectMailer]
GO
EXEC sp_addrolemember N'db_owner', N'CS2007\CSDMSvc'
GO
USE [MSCS_Admin]
GO
CREATE USER [CS2007\CSDMSvc] FOR LOGIN [CS2007\CSDMSvc]
GO
USE [MSCS_Admin]
GO
EXEC sp_addrolemember N'admin_reader_role', N'CS2007\CSDMSvc'
GO

-- CSHealthMonitorSvc
USE [MSCS_Admin]
GO
CREATE USER [CS2007\CSHealthMonitorSvc] FOR LOGIN [CS2007\CSHealthMonitorSvc]
GO
USE [MSCS_Admin]
GO
EXEC sp_addrolemember N'admin_reader_role', N'CS2007\CSHealthMonitorSvc'
GO

--
USE [CSharpSite_marketing]
GO
CREATE USER [CS2007\CSStageSvc] FOR LOGIN [CS2007\CSStageSvc]
GO
USE [CSharpSite_marketing]
GO
EXEC sp_addrolemember N'db_ddladmin', N'CS2007\CSStageSvc'
GO
USE [CSharpSite_marketing]
GO
EXEC sp_addrolemember N'mktg_staging_role', N'CS2007\CSStageSvc'
GO
USE [CSharpSite_marketing_lists]
GO
CREATE USER [CS2007\CSStageSvc] FOR LOGIN [CS2007\CSStageSvc]
GO
USE [CSharpSite_marketing_lists]
GO
EXEC sp_addrolemember N'db_datareader', N'CS2007\CSStageSvc'
GO
USE [CSharpSite_productcatalog]
GO
CREATE USER [CS2007\CSStageSvc] FOR LOGIN [CS2007\CSStageSvc]
GO
USE [CSharpSite_productcatalog]
GO
EXEC sp_addrolemember N'ctlg_CatalogWriterRole', N'CS2007\CSStageSvc'
GO
USE [CSharpSite_productcatalog]
GO
EXEC sp_addrolemember N'db_datareader', N'CS2007\CSStageSvc'
GO
USE [CSharpSite_productcatalog]
GO
EXEC sp_addrolemember N'db_datawriter', N'CS2007\CSStageSvc'
GO
USE [CSharpSite_productcatalog]
GO
EXEC sp_addrolemember N'db_ddladmin', N'CS2007\CSStageSvc'
GO
USE [CSharpSite_productcatalog]
GO
EXEC sp_addrolemember N'db_securityadmin', N'CS2007\CSStageSvc'
GO
USE [CSharpSite_productcatalog]
GO
EXEC sp_addrolemember N'Inventory_ReaderRole', N'CS2007\CSStageSvc'
GO
USE [CSharpSite_productcatalog]
GO
EXEC sp_addrolemember N'Inventory_WriterRole', N'CS2007\CSStageSvc'
GO
USE [CSharpSite_profiles]
GO
CREATE USER [CS2007\CSStageSvc] FOR LOGIN [CS2007\CSStageSvc]
GO
USE [CSharpSite_profiles]
GO
EXEC sp_addrolemember N'Profile_Schema_Manager', N'CS2007\CSStageSvc'
GO
USE [CSharpSite_transactionconfig]
GO
CREATE USER [CS2007\CSStageSvc] FOR LOGIN [CS2007\CSStageSvc]
GO
USE [CSharpSite_transactionconfig]
GO
EXEC sp_addrolemember N'Orders_Management', N'CS2007\CSStageSvc'
GO
USE [MSCS_Admin]
GO
CREATE USER [CS2007\CSStageSvc] FOR LOGIN [CS2007\CSStageSvc]
GO
USE [MSCS_Admin]
GO
EXEC sp_addrolemember N'admin_reader_role', N'CS2007\CSStageSvc'
GO
USE [MSCS_CatalogScratch]
GO
CREATE USER [CS2007\CSStageSvc] FOR LOGIN [CS2007\CSStageSvc]
GO
USE [MSCS_CatalogScratch]
GO
EXEC sp_addrolemember N'db_datareader', N'CS2007\CSStageSvc'
GO
USE [MSCS_CatalogScratch]
GO
EXEC sp_addrolemember N'db_datawriter', N'CS2007\CSStageSvc'
GO
USE [MSCS_CatalogScratch]
GO
EXEC sp_addrolemember N'db_ddladmin', N'CS2007\CSStageSvc'
GO 

 

Make sure to have the xcalcs_installer.exe installed before going on.

Assign CS Permissions VB Script:

' Declare the users
Dim users(4)
users(0) = "RunTimeUser"
users(1) = "CatalogWebSvc"
users(2) = "MarketingWebSvc"
users(3) = "OrdersWebSvc"
users(4) = "ProfilesWebSvc"

' Run the Load method
Load

Sub Load()

    ' Write permissions to the catalog auth role
    strObject = "C:\Inetpub\wwwroot\CatalogWebService\CatalogAuthorizationStore.xml"
    UpdatePermissions strObject, users(1)

    ' Write permissions to temporary ASP.NET folder
    strObject = "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files"
    For Each user IN users
        UpdatePermissions strObject, user
    Next

    ' Write permissions to the Windows temporary folder
    strObject = "C:\WINDOWS\Temp"
    For Each user IN users
        UpdatePermissions strObject, user
    Next

End Sub

' Update the permissions of the folder/file
Sub UpdatePermissions(strLocation, strUser)

    Set objShell = CreateObject("Wscript.Shell")
    ' Make sure to have the xcacls.vbs file available.  Download from:
    ' http://download.microsoft.com/download/f/7/8/f786aaf3-a37b-45ab-b0a2-8c8c18bbf483/xcacls_installer.exe
    objShell.Run "xcacls.vbs """ + strLocation + """ /G " + strUser + ":XW /E", 2, True

End Sub

These Scripts should make life a little easier when installing Commerce Server 2007. Enjoy!

Hat Tip Wade Wegner