Assigning SQL Server Database Roles

Commerce Server Business Desk users and run-time users must have sufficient permissions to access the SQL Server database that corresponds to the resource in order to access Commerce Server resources, such as the Product Catalog. For example, Business Desk users who modify shipping methods need access to the Transactions Config database.

When you configure your site to use Windows Authentication, it is recommended that create domain accounts for the following users and services, and then create SQL login accounts for each domain account.

Account user Sample account name Description
Runtime users Anonymous A user account for anonymous users visiting your Web site.
Business Desk users BDGroup A group account for all Business Desk users. Also known as design-time users.
Advanced Report group ReportAdvanced A group account for Business Desk users who must save dynamic reports, modify reports, and delete reports.
Segment Viewer group SegmentViewer A group account for Business Desk users who work with the Segment Viewer module.
DTS Task Import group DTSImport A group account for system administrators who run the DTS tasks to import and extend the Data Warehouse.
Direct Mailer service CSDM A user account for the Direct Mailer service.
List Manager service CSLM A user account for the List Manager service.
Predictor service CSPred A user account for the Predictor service.
Config COM+ application CSCOMPlus A user account for the Config COM+ application.

For instructions about creating these domain accounts, see Deploying Commerce Server Using Windows Authentication.

After you create a domain account, you run the Commerce Server security scripts that create the required database roles for Business Desk uses and run-time users. For instructions, see Overview of Scripts to Secure Your Databases.

This topic assumes you have created the domain accounts and run the Commerce Server security scripts. It explains the next steps: creating the SQL login account, and then assigning the domain account to the database roles.

To create a SQL login account

  1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
  2. In SQL Server Enterprise Manager, expand the nodes to the following path: Microsoft SQL Servers/SQL Server Group/<computer name>/Security.
  3. Right-click Login, and then click New Login.
  4. In the SQL Server Login Properties - New Login screen, on the General tab, do the following:
    Use this To do this
    Name Type the name of the domain user account (for example, Anonymous, a sample user account for your run-time users).
    Windows Authentication Verify that this option is selected.
    Domain Select the domain of the account from the drop-down list.
    Grant access Verify that this option is selected for security access.
    Database Select master as the database for this login from the drop-down list.
    Language Select the default language for this login from the drop-down list.
  5. Click OK.
  6. Repeat these steps until you have created a SQL Server login account for the following domain accounts: Business Desk users (a group account), List Manager service, Predictor service, Direct Mailer service, and the Config COM+ Application.

To assign SQL Server database roles to a login

  1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
  2. In SQL Server Enterprise Manager, expand Security, and then click Logins.
  3. Right-click a SQL login account, and then click Properties.
  4. In the SQL Server Login Properties dialog box, click the Database Access tab.
  5. In the top box, select a database, and then in the bottom box, specify the role for the account.
  6. Repeat these steps until all the required accounts are assigned membership to the database roles they need, and then click OK.

Ee798056.note(en-US,CS.20).gifNote

  • If you use SQL Server authentication, the resource connection strings contain the SQL login and password. When a connection string uses SQL Server authentication, every time the connection string is used, the login that is in the connection string is used. As a result, you cannot connect to a database using different logins with different access permissions.

See Also

Scripts to Secure Your Databases

Scripts for Securing Databases Accessed by Reports

Scripts for Securing Databases Accessed by DTS Tasks

Deploying Commerce Server Using Windows Authentication

Securing the Administration Database

Securing the Direct Mailer Database

Securing a Predictor Deployment

Copyright © 2005 Microsoft Corporation.
All rights reserved.