Share via


Securing the Profiles 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.

Systems administrators also require access to the Profiles database and should be assigned to the db_owner role.

To help you secure the Profiles database, Commerce Server includes two security scripts: ProfileReaderRole.sql and ProfileWriterRole.sql. These scripts are located in the Program Files\Microsoft Commerce Server\Support folder.

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

  • ProfileReaderRole. Assign run-time users to this role.
  • ProfileWriterRole. Assign design-time users to this role.

To create the ProfileReaderRole and the ProfileWriterRole

  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 Profiles 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 ProfileReaderRole.

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

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

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

Table name ProfileReaderRole
(Run-time users)
ProfileWriterRole
(Business Desk users)
Addresses
Select Select
Insert
Delete
Update
BlanketPOs
Select Select
Insert
Delete
Update
ClassDef
Select Select
Update
ClsAttrib
Select Select
ColDef
Select Select
CommerceServerCatalogs
Select Select
ES_ExprExprDeps
Select Select
Update
Insert
Delete
ES_ExprInfo
Select Select
Update
Insert
Delete
ES_ExprProfDeps
Select Select
Update
Insert
Delete
GroupMem
Select Select
Update
Insert
MemAttrib
Select Select
Update
Insert
Delete
MemberDef
Select Select
Update
Insert
Delete
OrganizationObject
Select
Insert
Update
Select
Insert
Delete
Update
RelDef
Select Select
SourceAttrib
Select Select

Insert

Delete

Update

SourceDef
Select Select

Insert

Delete

Update

TableDef
Select Select
Insert
Delete
Update
UserObject
Select
Insert
Update
Select
Insert
Delete
Update

Profiles Stored Procedures

To secure the Profiles stored procedures, the scripts grant permissions to the ProfileReaderRole and ProfileWriterRole scripts as shown in the following table.

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

  • In the Catalogs database, you must grant the CatalogSet_Info table Select permissions for the Business Desk user account. Otherwise, catalog sets used by the Organization and Profile modules will fail to load.
Profiles stored procedures ProfileReaderRole
(Run-Time users)
ProfileWriterRole
(Business Desk users)
sp_CheckDeleteExpr
No Yes
sp_CheckExprExprDeps
No Yes
sp_CheckExprStore
Yes Yes
sp_DelProfile
No Yes
sp_DelProfileCatalog
No Yes
sp_DelProfileGroup
No Yes
sp_DelProfileGroupHelper
No Yes
sp_DelProfileHelper
No Yes
Sp_DelProfileProperty
No Yes
sp_DelProfilePropHelper
No Yes
sp_DelDataMember
No Yes
sp_DelDataObject
No Yes
sp_DelDataSource
No Yes
sp_EnsureEmptyStore
No Yes
sp_ExprIDFromName
Yes Yes
sp_ExprNameFromID
Yes Yes
sp_GetProfileCustomProps
No Yes
sp_GetDataSourceDepends
No Yes
sp_GetDataSourceInfo
Yes Yes
sp_GetProfileCatalogInfo
Yes Yes
sp_GetProfileCatalogs
No Yes
sp_GetProfileDomains
No Yes
sp_GetProfileInfo
Yes Yes
sp_GetProfileMemAttrs
No Yes
sp_GetProfileMember
No Yes
sp_GetProfileProps
Yes Yes
sp_GetProfileSources
No Yes
sp_ImportExprInfo
No Yes
sp_UpdateExprModDate
No Yes

See Also

Credentials for an Active Directory Profile Data Source

Copyright © 2005 Microsoft Corporation.
All rights reserved.