-- ===================================================================
-- Author:      Wade Wegner
-- Create date: 06/30/2007
-- Description: Create the SQL logins and assign them to roles
-- File Name:   CreateCSLoginsAndAssignRolesForNewSites.sql
-- ===================================================================

----------------------------------------------------------------------
-- Create database accounts in SQL Server 2005 for additional CS sites
----------------------------------------------------------------------

--CatalogWebSvc
USE [StarterSite_productcatalog]
GO
CREATE USER [CS2007\CatalogWebSvc] FOR LOGIN [CS2007\CatalogWebSvc]
GO
USE [StarterSite_productcatalog]
GO
EXEC sp_addrolemember N'ctlg_CatalogWriterRole', N'CS2007\CatalogWebSvc'
GO
USE [StarterSite_productcatalog]
GO
EXEC sp_addrolemember N'db_datareader', N'CS2007\CatalogWebSvc'
GO
USE [StarterSite_productcatalog]
GO
EXEC sp_addrolemember N'db_datawriter', N'CS2007\CatalogWebSvc'
GO
USE [StarterSite_productcatalog]
GO
EXEC sp_addrolemember N'db_ddladmin', N'CS2007\CatalogWebSvc'
GO
USE [StarterSite_productcatalog]
GO
EXEC sp_addrolemember N'db_securityadmin', N'CS2007\CatalogWebSvc'
GO
USE [StarterSite_productcatalog]
GO
EXEC sp_addrolemember N'Inventory_ReaderRole', N'CS2007\CatalogWebSvc'
GO
USE [StarterSite_productcatalog]
GO
EXEC sp_addrolemember N'Inventory_WriterRole', N'CS2007\CatalogWebSvc'
GO
-- MarketingWebSvc
USE [StarterSite_marketing]
GO
CREATE USER [CS2007\MarketingWebSvc] FOR LOGIN [CS2007\MarketingWebSvc]
GO
USE [StarterSite_marketing]
GO
EXEC sp_addrolemember N'mktg_marketingService_role', N'CS2007\MarketingWebSvc'
GO
USE [StarterSite_marketing]
GO
EXEC sp_addrolemember N'mktg_promoCodeGenerator_role', N'CS2007\MarketingWebSvc'
GO
USE [StarterSite_marketing_lists]
GO
CREATE USER [CS2007\MarketingWebSvc] FOR LOGIN [CS2007\MarketingWebSvc]
GO
USE [StarterSite_marketing_lists]
GO
EXEC sp_addrolemember N'db_owner', N'CS2007\MarketingWebSvc'
GO
USE [StarterSite_productcatalog]
GO
CREATE USER [CS2007\MarketingWebSvc] FOR LOGIN [CS2007\MarketingWebSvc]
GO
USE [StarterSite_productcatalog]
GO
EXEC sp_addrolemember N'ctlg_CatalogReaderRole', N'CS2007\MarketingWebSvc'
GO
USE [StarterSite_profiles]
GO
CREATE USER [CS2007\MarketingWebSvc] FOR LOGIN [CS2007\MarketingWebSvc]
GO
USE [StarterSite_profiles]
GO
EXEC sp_addrolemember N'Profile_Reader', N'CS2007\MarketingWebSvc'
GO
USE [StarterSite_profiles]
GO
EXEC sp_addrolemember N'Profile_Schema_Reader', N'CS2007\MarketingWebSvc'
GO
-- OrdersWebSvc
USE [StarterSite_marketing]
GO
CREATE USER [CS2007\OrdersWebSvc] FOR LOGIN [CS2007\OrdersWebSvc]
GO
USE [StarterSite_marketing]
GO
EXEC sp_addrolemember N'db_ddladmin', N'CS2007\OrdersWebSvc'
GO
USE [StarterSite_marketing]
GO
EXEC sp_addrolemember N'mktg_runtime_role', N'CS2007\OrdersWebSvc'
GO
USE [StarterSite_productcatalog]
GO
CREATE USER [CS2007\OrdersWebSvc] FOR LOGIN [CS2007\OrdersWebSvc]
GO
USE [StarterSite_productcatalog]
GO
EXEC sp_addrolemember N'ctlg_CatalogReaderRole', N'CS2007\OrdersWebSvc'
GO
USE [StarterSite_productcatalog]
GO
EXEC sp_addrolemember N'Inventory_ReaderRole', N'CS2007\OrdersWebSvc'
GO
USE [StarterSite_profiles]
GO
CREATE USER [CS2007\OrdersWebSvc] FOR LOGIN [CS2007\OrdersWebSvc]
GO
USE [StarterSite_profiles]
GO
EXEC sp_addrolemember N'Profile_Reader', N'CS2007\OrdersWebSvc'
GO
USE [StarterSite_profiles]
GO
EXEC sp_addrolemember N'Profile_Schema_Reader', N'CS2007\OrdersWebSvc'
GO
USE [StarterSite_transactionconfig]
GO
CREATE USER [CS2007\OrdersWebSvc] FOR LOGIN [CS2007\OrdersWebSvc]
GO
USE [StarterSite_transactionconfig]
GO
EXEC sp_addrolemember N'Orders_Management', N'CS2007\OrdersWebSvc'
GO
USE [StarterSite_transactions]
GO
CREATE USER [CS2007\OrdersWebSvc] FOR LOGIN [CS2007\OrdersWebSvc]
GO
USE [StarterSite_transactions]
GO
EXEC sp_addrolemember N'Orders_Management', N'CS2007\OrdersWebSvc'
GO
USE [StarterSite_transactions]
GO
EXEC sp_addrolemember N'Orders_Runtime', N'CS2007\OrdersWebSvc'
GO
-- ProfilesWebSvc
USE [StarterSite_profiles]
GO
CREATE USER [CS2007\ProfilesWebSvc] FOR LOGIN [CS2007\ProfilesWebSvc]
GO
USE [StarterSite_profiles]
GO
EXEC sp_addrolemember N'Profile_Runtime', N'CS2007\ProfilesWebSvc'
GO
USE [StarterSite_profiles]
GO
EXEC sp_addrolemember N'Profile_Schema_Manager', N'CS2007\ProfilesWebSvc'
GO
-- RunTimeUser
USE [StarterSite_marketing]
GO
CREATE USER [CS2007\RunTimeUser] FOR LOGIN [CS2007\RunTimeUser]
GO
USE [StarterSite_marketing]
GO
EXEC sp_addrolemember N'db_ddladmin', N'CS2007\RunTimeUser'
GO
USE [StarterSite_marketing]
GO
EXEC sp_addrolemember N'mktg_runtime_role', N'CS2007\RunTimeUser'
GO
USE [StarterSite_marketing_lists]
GO
CREATE USER [CS2007\RunTimeUser] FOR LOGIN [CS2007\RunTimeUser]
GO
USE [StarterSite_marketing_lists]
GO
EXEC sp_addrolemember N'db_datareader', N'CS2007\RunTimeUser'
GO
USE [StarterSite_productcatalog]
GO
CREATE USER [CS2007\RunTimeUser] FOR LOGIN [CS2007\RunTimeUser]
GO
USE [StarterSite_productcatalog]
GO
EXEC sp_addrolemember N'ctlg_CatalogReaderRole', N'CS2007\RunTimeUser'
GO
USE [StarterSite_productcatalog]
GO
EXEC sp_addrolemember N'Inventory_RuntimeRole', N'CS2007\RunTimeUser'
GO
USE [StarterSite_profiles]
GO
CREATE USER [CS2007\RunTimeUser] FOR LOGIN [CS2007\RunTimeUser]
GO
USE [StarterSite_profiles]
GO
EXEC sp_addrolemember N'Profile_Runtime', N'CS2007\RunTimeUser'
GO
USE [StarterSite_profiles]
GO
EXEC sp_addrolemember N'Profile_Schema_Reader', N'CS2007\RunTimeUser'
GO
USE [StarterSite_transactionconfig]
GO
CREATE USER [CS2007\RunTimeUser] FOR LOGIN [CS2007\RunTimeUser]
GO
USE [StarterSite_transactionconfig]
GO
EXEC sp_addrolemember N'Orders_Runtime', N'CS2007\RunTimeUser'
GO
USE [StarterSite_transactions]
GO
CREATE USER [CS2007\RunTimeUser] FOR LOGIN [CS2007\RunTimeUser]
GO
USE [StarterSite_transactions]
GO
EXEC sp_addrolemember N'Orders_Runtime', N'CS2007\RunTimeUser'
GO
-- CSDMSvc
USE [StarterSite_marketing]
GO
CREATE USER [CS2007\CSDMSvc] FOR LOGIN [CS2007\CSDMSvc]
GO
USE [StarterSite_marketing]
GO
EXEC sp_addrolemember N'mktg_directmailer_role', N'CS2007\CSDMSvc'
GO
USE [StarterSite_marketing_lists]
GO
CREATE USER [CS2007\CSDMSvc] FOR LOGIN [CS2007\CSDMSvc]
GO
USE [StarterSite_marketing_lists]
GO
EXEC sp_addrolemember N'db_owner', N'CS2007\CSDMSvc'
GO
USE [StarterSite_profiles]
GO
CREATE USER [CS2007\CSDMSvc] FOR LOGIN [CS2007\CSDMSvc]
GO
USE [StarterSite_profiles]
GO
EXEC sp_addrolemember N'Profile_Reader', N'CS2007\CSDMSvc'
GO
USE [StarterSite_profiles]
GO
EXEC sp_addrolemember N'Profile_Schema_Reader', N'CS2007\CSDMSvc'
GO
USE [StarterSite_marketing]
GO
CREATE USER [CS2007\CSStageSvc] FOR LOGIN [CS2007\CSStageSvc]
GO
USE [StarterSite_marketing]
GO
EXEC sp_addrolemember N'db_ddladmin', N'CS2007\CSStageSvc'
GO
USE [StarterSite_marketing]
GO
EXEC sp_addrolemember N'mktg_staging_role', N'CS2007\CSStageSvc'
GO
USE [StarterSite_marketing_lists]
GO
CREATE USER [CS2007\CSStageSvc] FOR LOGIN [CS2007\CSStageSvc]
GO
USE [StarterSite_marketing_lists]
GO
EXEC sp_addrolemember N'db_datareader', N'CS2007\CSStageSvc'
GO
USE [StarterSite_productcatalog]
GO
CREATE USER [CS2007\CSStageSvc] FOR LOGIN [CS2007\CSStageSvc]
GO
USE [StarterSite_productcatalog]
GO
EXEC sp_addrolemember N'ctlg_CatalogWriterRole', N'CS2007\CSStageSvc'
GO
USE [StarterSite_productcatalog]
GO
EXEC sp_addrolemember N'db_datareader', N'CS2007\CSStageSvc'
GO
USE [StarterSite_productcatalog]
GO
EXEC sp_addrolemember N'db_datawriter', N'CS2007\CSStageSvc'
GO
USE [StarterSite_productcatalog]
GO
EXEC sp_addrolemember N'db_ddladmin', N'CS2007\CSStageSvc'
GO
USE [StarterSite_productcatalog]
GO
EXEC sp_addrolemember N'db_securityadmin', N'CS2007\CSStageSvc'
GO
USE [StarterSite_productcatalog]
GO
EXEC sp_addrolemember N'Inventory_ReaderRole', N'CS2007\CSStageSvc'
GO
USE [StarterSite_productcatalog]
GO
EXEC sp_addrolemember N'Inventory_WriterRole', N'CS2007\CSStageSvc'
GO
USE [StarterSite_profiles]
GO
CREATE USER [CS2007\CSStageSvc] FOR LOGIN [CS2007\CSStageSvc]
GO
USE [StarterSite_profiles]
GO
EXEC sp_addrolemember N'Profile_Schema_Manager', N'CS2007\CSStageSvc'
GO
USE [StarterSite_transactionconfig]
GO
CREATE USER [CS2007\CSStageSvc] FOR LOGIN [CS2007\CSStageSvc]
GO
USE [StarterSite_transactionconfig]
GO
EXEC sp_addrolemember N'Orders_Management', N'CS2007\CSStageSvc'
GO
EXEC sp_addrolemember N'admin_reader_role', N'CS2007\CSStageSvc'
GO