Securing the Campaigns Database

  • Article

It is strongly recommended that you use Windows Authentication for access to your databases. When you configure your database connection strings for Windows Authentication, you must assign Business Desk users and run-time users (who use an anonymous domain account) the appropriate level of access to your databases.

To help you secure the Campaigns database, Commerce Server includes two security scripts: CampaignReaderRole.sql and CampaignWriterRole.sql. These scripts are located in the Program Files\Microsoft Commerce Server\Support folder.

These scripts create two roles on the Campaigns database, and assign the necessary permissions to the tables and stored procedures:

  • CampaignReaderRole. Assign run-time users to this role.
  • CampaignWriterRole. Assign design-time users to this role.

Ee785172.important(en-US,CS.20).gifImportant

  • Business Desk users must be assigned to the CampaignWriterRole and the db_datareader role on the Campaigns database.
  • To enable Business Desk users to delete campaign items, they must belong to the public role on the Direct Mailer database.

To create the CampaignReaderRole and the CampaignWriterRole

  1. Click Start, point to Programs, point to Microsoft SQL Server, and then click SQL Query Analyzer.

  2. In the Connect to SQL Server dialog box, specify the appropriate SQL server.

  3. In Query Analyzer, in the database drop-down box, select the Campaigns database.

  4. Click File, and then click Open.

  5. Navigate to the scripts located in the Program Files\Microsoft Commerce Server\Support folder, and select CampaignReaderRole.

    The script opens and the code appears in the Query Analyzer window.

  6. On the toolbar, click Run to run the script against the selected database.

  7. Repeat these steps to run the CampaignWriterRole script.

  8. After you create the roles, assign the anonymous run-time user account and the Business Desk group account to the appropriate roles. For instructions, see Assigning SQL Server Database Roles.

The scripts create the roles and grant permissions on the following Campaign tables and stored procedures.

Table name CampaignReaderRole
(Run-time users)		 CampaignWriterRole
(Business Desk users)		 
Ad_item
 Select Select
Update
Insert		 
Campaign
 Select Select
Update
Insert		 
Campaign_item
 Select Select
Update
Insert		 
Campaign_item_types
 Select Select
Update
Insert		 
Campaigns_version
 Select Select 
Creative
 Select Select
Update
Insert		 
Creative_property
 Select Select
Update
Insert		 
Creative_property_value
 Select Select
Update
Insert
Delete		 
Creative_size
 Select Select
Update
Insert
Delete		 
Creative_type
 Select Select 
Creative_type_xref
 Select Select 
Customer
 Select Select
Update
Insert		 
Dm_item
 No access Select
Update
Insert
Delete		 
Event_type
 Select Select 
Industry_code
 Select Select
Update
Insert
Delete		 
Order_discount
 Select Select
Update
Insert		 
Order_discount_expression
 Select Select
Update
Insert
Delete		 
Order_discount_misc
 Select Select
Update
Insert
Delete		 
Page_group
 Select Select
Update
Insert
Delete		 
Page_group_xref
 Select Select
Update
Insert
Delete		 
Performance_total
 Select
Update
Insert		 Select 
Target
 Select Select
Update
Insert
Delete		 
Target_group
 Select Select
Update
Insert
Delete		 
Target_group_xref
 Select Select
Update
Insert
Delete		 
Virtual_directory
 Select
Update
Insert		 Select
Delete

Campaigns Stored Procedures

The campaigns security scripts grant permissions on the following stored procedures as shown.

Campaigns stored procedures CampaignReaderRole
(Run-time users)		 CampaignWriterRole
(Business Desk users)		 
sp_camp_item_archive
 No Yes 
sp_clean_archived_campaigns
 No Yes 
sp_clean_delete_campaign
 No Yes 
sp_clean_delete_campitem
 No Yes 
sp_clean_delete_customer
 No Yes 
sp_cs_campitem_add
 No Yes 
sp_cs_campitem_delete
 No Yes 
sp_cs_campitem_update
 No Yes 
sp_cs_mail_add
 No Yes 
sp_cs_mail_delete
 No Yes 
sp_cs_mail_schedid
 No Yes 
sp_cs_mail_update
 No Yes 
sp_DeleteCampaign
 No Yes 
sp_DeleteCampaignItem
 No Yes 
sp_DeleteCustomer
 No Yes 
sp_dm_item_delete
 No Yes 
sp_dm_item_edit
 No Yes 
sp_dm_item_new
 No Yes 
sp_GetCampItemGUID
 No Yes 
sp_InsertAdItem
 No Yes 
sp_InsertCampaign
 No Yes 
sp_InsertCampaignItem
 No Yes 
sp_InsertCPValue
 No Yes 
sp_InsertCreative
 No Yes 
sp_InsertCustomer
 No Yes 
sp_InsertDiscount
 No Yes 
sp_InsertDiscount2
 No Yes 
sp_InsertDiscountMisc
 No Yes 
sp_InsertPageGroup
 No Yes 
sp_InsertPageGroupMain
 No Yes 
sp_InsertTargetGroup
 No Yes 
sp_InsertTargetGroupMain
 No Yes 
sp_InsertTargetMain
 No Yes 
sp_UndeleteCampaign
 No Yes 
sp_UndeleteCampaignItem
 No Yes 
sp_UndeleteCustomer
 No Yes 
sp_update_performance_total
 Yes Yes 
sp_update_vdir
 Yes Yes 
sp_UpdateAdItem
 No Yes 
sp_UpdateCampaign
 No Yes 
sp_UpdateCampaignItem
 No Yes 
sp_UpdateCreative
 No Yes 
sp_UpdateCustomer
 No Yes 
sp_UpdateDiscount
 No Yes 
sp_UpdateDiscount2
 No Yes 
sp_UpdateDiscountMisc
 No Yes 
sp_UpdateTargetGroupMain
 No Yes 
sp_UpdateTargetMain
 No Yes

List Manager: Dynamic Database Objects

The List Manager Service, which runs under a service account, creates a new table for each list that is imported. For example, it might create the following table:

LM_MAILLIST_40FBCA60_ABE8_4392_BB0B_2B02BA95AE03

The first time the List Manager service is invoked, it finds the appropriate database using its connection string and checks whether the database has the List Manager tables. If it does not, it creates the following two tables:

lm_master
lm_master_operations

When the Direct Mailer job runs and calls the List Manager service, the List Manager also creates tables in the Direct Mailer database.

The following table shows the permissions required by List Manager, Predictor (Commerce Server 2002 Enterprise Edition), Business Desk users, and the run-time users.

Table name Run-time users Business Desk users List Manager Service account Predictor Service account
Campaigns database For security requirements, see the Campaigns tables. For security requirements, see the Campaigns tables. db_owner role db_ddladmin
db_datawriter
db_datareader
Direct Mailer database For security requirements, see Securing the Direct Mailer Database. For security requirements, see Securing the Direct Mailer Database. db_owner role No access 
lm_master
 No No Select
Update
Insert
Delete		 Select
Update
Insert
Delete		 
lm_master_operations
No No Select
Update
Insert
Delete		 Select
Update
Insert
Delete		 
LM_MAILLIST_<GUID>
 No No Select
Update
Insert
Delete		 Select
Update
Insert
Delete

List Manager Stored Procedures

Stored procedure Run-Time users Business Desk users ListManager Service Account 
sp_lm_CheckLMStore
 No No Yes 
sp_lm_DeleteListAndData
 No No Yes 
sp_lm_GetSchema
 No No Yes 
sp_lm_LoadAllLists
 No No Yes 
sp_lm_LoadAllOperations
 No No Yes 
sp_lm_LoadListById
 No No Yes 
sp_lm_LoadListByName
 No No Yes 
sp_lm_LoadOperationInfo
 No No Yes 
sp_lm_SaveListDetails
 No No Yes 
sp_lm_SaveOperationDetails
 No No Yes 
sp_lm_SetListCreationTime
 No No Yes 
sp_lm_SetOpCompletionTime
 No No Yes 
sp_lm_UpdateListRecentOperLid
 No No Yes 
sp_lm_UpdateListStatus
 No No Yes 
sp_lm_UpdateOperStatus
 No No Yes

After these tables and stored procedures are created, if you change the account for List Manager, you must remember to grant the appropriate permissions to the new account.

See Also

Permissions Required to Export Static Reports and Segments to List Manager

List Manager Service Account

Copyright © 2005 Microsoft Corporation.
All rights reserved.