Share via


Securing the Transactions Database

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 levels of access to your databases.

To help you secure the Transactions database, Commerce Server includes two security scripts: TransactionsReaderRole.sql and TransactionsWriterRole.sql. These scripts are located in the Program Files\Microsoft Commerce Server\Support folder**.**

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

  • TransactionsReaderRole. Assign run-time users to this role.
  • TransactionsWriterRole. Assign design-time users to this role.

To create and run the TransactionsReaderRole and TransactionsWriterRole

  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 Transactions 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 TransactionsReaderRole.

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

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

  7. Repeat these steps to run the TransactionsWriterRole 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 TransactionsReaderRole and TransactionsWriterRole scripts create the two roles and grant permissions on the following Transactions tables and stored procedures.

Table name TransactionsReaderRole
(Run-time users)
TransactionsWriter
Role
(Business Desk users)
BasketGroup
Select
Insert
Update
Delete
Select
Delete
CatalogProducts_AuctionBid
Select
Insert
Update
Delete
Select
Insert
Update
Delete
CatalogProducts_AuctionItem
No access Select
Insert
Update
Delete
Counters
Select
Insert
Update
No access
OrderFormHeader
Select
Insert
Update
Select
Delete
OrderFormLineItems
Select
Insert
Update
Select
Delete
OrderGroup
Select
Insert
Update
Select
Delete
OrderGroupAddresses
Select
Insert
Update
Select
Delete
TransCategory
No access No access
*TransDimension
No access No access
UserFilter
Select
Insert
Update
Delete
No access

*TransDimension is used by the Transaction data import Data Transformation Services (DTS) task. For more information, see ETL Process for Transaction Data Import DTS Task.

Stored Procedure

There is only one Transactions stored procedure: OrderSummaryProc. Neither run-time users nor design-time users require access. This stored procedure is called by the OrderGroupManager.GetOrderSummary script.

Copyright © 2005 Microsoft Corporation.
All rights reserved.