Scripts for Securing Databases Accessed by DTS Tasks

  • Article

Commerce Server includes security scripts that you run against the Commerce Server runtime databases and the Administration database. These scripts are located in the Program Files\Microsoft Commerce Server\Support folder.

These scripts create the DTS_ImportRole on each database, and assigns the role the appropriate access permissions on the specified database.

The following table lists the DTS task security scripts, and the databases you run them against.

Script Run against this database
Dts_AdminDB_SecurityRole.sql Administration
Dts_CommerceDB_Campaign_SecurityRole.sql Campaigns
Dts_CommerceDB_Catalog_SecurityRole.sql Catalog
Dts_CommerceDB_Transaction_SecurityRole.sql Transactions
Dts_CommerceDB_UPM_SecurityRole.sql Profiles

The Commerce Server runtime database tables can reside in one database, or each resource can have tables in its own database.

These scripts are designed to work with SQL Server, for data stored in the Commerce Server runtime databases and the Administration database. They are not designed for use with Active Directory or with an Oracle database.

Ee823536.note(en-US,CS.20).gifNotes

  • The DTS_ImportRole does not have permissions to create a non-Commerce Server application using the Data Warehouse Configurator. A user who belongs to the DTS_ImportRole can perform the steps to create a non-Commerce Server application, but no application will be created.

    To create a non-Commerce Server application using the Data Warehouse Configurator, you must be a member of the Administrators group.

  • To run the Data Warehouse Import Wizard and save a package, your account must have Select, Insert, and Update permissions on the msdb database of the SQL Server on which you are running the DTS tasks. The msdb database is used by SQL Server Agent for scheduling alerts and jobs, and it is accessed to save the DTS package. In addition, assign the same permissions on the sysDTSPackage object, which allows users to save a DTS package.

  • If the Commerce Server DTS tasks are being scheduled by the SQL Server Agent, then the SQL Server Agent service should be run under a Windows Service account that is a member of the DTS_ImportRole.

  • If you are running the Commerce Server DTS tasks directly from Commerce Server Manager, Windows Script Host, or SQL Server Enterprise Manager, the Windows login account should be a member of the DTS_ImportRole.

To run the DTS task security scripts

  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 database you want to run the script against.

  4. Click File, and then click Open.

  5. Navigate to the scripts located in the Program Files\Microsoft Commerce Server\Support folder, and select the DTS security script you want to run.

    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 remaining DTS task security scripts on the appropriate databases.

  8. After you create the DTS_ImportRole on each database, assign to each role the account of the appropriate users who will run the DTS tasks. For instructions, see Assigning SQL Server Database Roles.

See Also

Permissions Required to Run DTS Tasks

Copyright © 2005 Microsoft Corporation.
All rights reserved.