Data Warehouse SQL Server Database

  • Article

This topic provides security reference information for the following Data Warehouse SQL Server database components that require security:

Data Warehouse Tables

Data Warehouse Views

Data Warehouse Functions

The data in the SQL Server database is accessed when the following tasks are performed:

  • You run the Commerce Server DTS tasks. For the permissions required to run the DTS tasks, see Permissions Required to Run DTS Tasks.

  • Business Desk users run static reports. To secure the Data Warehouse SQL Server database, you use the Commerce Server security scripts to create database roles for Business Desk users who run reports against the database. For more information, see Scripts for Securing Databases Accessed by Reports.

    You also must assign the db_owner role in the Data Warehouse database for the account specified in the Commerce Server Report Manager COM+ component. For instructions, see Changing the Report Manager Account.

  • The Predictor service builds Prediction and Segment models. The Predictor service account must be assigned to the db_datareader, db_datawriter, and db_ddladmin roles on the Data Warehouse. For information, see Securing a Predictor Deployment.

Data Warehouse Tables

The following table lists specific Data Warehouse tables that Business Desk users (design-time users) must access to run reports. For a list of which reports and tasks require specific permissions, see ReportViewer.sql Script and ReportAdvanced.sql Script.

Table Design-time user access 
GroupReportDbObject
 Select
Insert
Delete		 
GroupReportJob
 Select
Insert
Delete		 
GroupReportStatus
 Select
Insert
Delete		 
LinkSiteRegisteredUserRel
 Select 
PredictorDataTables
 Select
Update		 
PredictorModelCfgs
 Select
Update		 
PredictorModels
 Select
Update		 
RegisteredUser
 Select
Insert		 
Report
 Select
Insert		 
ReportDimension
 Select
Insert
Delete		 
ReportInstanceDbObject
 Select
Insert		 
ReportInstanceParam
 Select
Insert		 
ReportParam
 Select
Insert
Delete		 
ReportStatus
 Select
Insert
Delete

Data Warehouse Views

The following Commerce Server views are used by dynamic reports that run against the SQL Server database to populate cubes and dimensions inside of Analysis Services.

Grant the account that you define for the Analysis Server (OLAP database) Select access to all of these views. No other role or account requires access to these views. For instructions, see Configuring the Analysis Server Account.

CampaignItemDimensionView
csdw_BuyerTypeDimensionView
csdw_CampaignEventsDXView
csdw_CampaignEventsFactView
csdw_SalesToUser
csdw_UserStatusViewLU
csdw_UserStatusViewRU
csdw_UserTypeDimensionView
csdw_VisitByUser
csdw_VisitInfoDXView
csdw_VisitInfoFactView
csdw_VisitTypeDimensionView
DateDimensionView
EventsDimensionView
FirstRequest
IsRequestDimensionView
PageGroupDimensionView
RegisteredUserDimensionView
SiteDimensionView
Trans_Predictor
UserBrowsingtoPurchaseFactView
UserRegistrationFactView

Data Warehouse Functions

The following Commerce Server functions are used by the Report preparation DTS task. The Business Desk user does not require access to them.

The Report preparation task requires Exec permissions for these functions. You do not need to do anything to enable Exec permissions for this DTS task, because you will have the necessary permissions as part of configuring your account to run the other DTS tasks.

csdw_fnGetDataTypeString
csdw_fnGetValAsSQLConstant
csdw_fnVarBinToHexStr

Copyright © 2005 Microsoft Corporation.
All rights reserved.