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

----------------------------------------------
-- Create database accounts in SQL Server 2005
----------------------------------------------

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