Scripts for Securing Databases Accessed by Reports

  • Article

Commerce Server provides the following two scripts that you can use to create two report roles:

  • ReportViewer.sql. Use this script to create the ReportViewerRole on the Data Warehouse SQL Server database. The ReportViewerRole has permissions for Business Desk users to perform the following tasks:

    • Run and view dynamic and static reports
    • Delete completed static reports
    • Export static reports

    To export static reports, the user also needs permissions to access the lm_master and lm_master_operation tables in the Commerce database. These tables are only created when a report is exported.

    In addition, the List Manager service account must be mapped to the dbo alias for the Data Warehouse. For more information, see Permissions Required to Export Static Reports and Segments to List Manager.

  • ReportAdvanced.sql. Use this script to create the ReportAdvancedRole on the Data Warehouse SQL Server database. The ReportAdvancedRole has permissions for Business Desk users to perform the following tasks:

    • Save dynamic reports
    • Modify existing non-system dynamic reports
    • Delete non-system dynamic and static reports

    This script adds itself as a member to ReportViewerRole, so users in the ReportAdvancedRole will inherit all permissions of ReportViewerRole.

These scripts do not include the necessary permissions for the Commerce database. You must assign the CREATE TABLE permissions on the Commerce database.

These scripts are located in the \Program Files\Microsoft Commerce Server\Support folder.

To run the ReportViewer and ReportAdvanced 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 Data Warehouse SQL Server 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 ReportViewer.

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

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

  7. Repeat these steps to run the ReportAdvanced script.

    The ReportAdvanced script adds itself as a member to ReportViewerRole, so users in the ReportAdvancedRole will inherit all permissions of ReportViewerRole.

  8. After you create the roles, assign the Business Desk group account to the ReportViewerRole, and then assign the ReportAdvanced account to the ReportAdvancedRole. For instructions, see Assigning SQL Server Database Roles.

Copyright © 2005 Microsoft Corporation.
All rights reserved.