Separating Sitecore 6.5 Delivery and CMS Database users

1
votes

We've been asked to separate as much as possible the database users between our CMS server and Delivery server. I've not found anything regarding permissions for the different Sitecore databases for this purpose anywhere, so thought I would ask here. Here is the plan based on what I gather the purposes for the different databases:

CMS User: read/execute to Sitecore_Analytics, read/write/execute access to Sitecore_Master, Sitecore_Web, and Sitecore_Core

Delivery User: no access to Sitecore_Master, read/execute access to Sitecore_Web and Sitecore_Core, read/write/execute access to Sitecore_Analytics

Note that we do not have any user account management on the public site, only on the CMS for content management. Does this permission scheme seem feasible?

1
sql-serverpermissionssitecoresitecore6
Is the error in the logs of CM or CD? And is this when you manually publish or have you set up automated publishing? - jammykam
It's on the CM server. - Levi Wallach
I went back to the scalability document and made some additions I'd missed (including adding a ScalabilitySettings.config file). However with that file present, when I publish, I get an SQLexception Invalid column name 'Stamp'. This column however is in my sitecoremaster and sitecoreweb databases. Eventqueue was previously disabled and confirmed that setting this to true in conf scalabilitysettings.config is what causes that exception. - Levi Wallach
I'm wondering if I need to set this server up for multiple instances? Currently I have two application pools and two corresponding sites in IIS, one for the CD and one for the CM. Does this mean there are two instances? This is our development environment. Our production environment uses two completely separate machines, and is set to single instance and publishing works fine... - Levi Wallach
Yes, that is 2 instances. Your prod most likely has the scalability settings configured, but it should largely work even without that, just some of the event processes would be out of whack, should not affect publishing afaik. - jammykam

1 Answers

1
votes

The permissions required for the various database are listed in Microsoft SQL Server Configuration section (page 54) of the Sitecore Installation Guide, including details about the role membership and permissions for executing stored procedures.

The content delivery server does not need any access to the master database. Remove this from the connectionstrings and then enable the "SwitchMasterToWeb.config" in the App_Config/Includes folder to change any references from the master to web database.

EDIT

Sitecore 6.5 does not have a SwitchToMaster.config file by default, have a read of the Scaling Guide Section 4.6 which provides details on manually removing the references, as well as a link to the config file for download.

END EDIT

You may also want to have a read of the Security Hardening Guide to make sure you have other areas covered.