January 23, 2017

Setting Sitecore SQL Server Database Permissions

By: Craig Taylor
January 23, 2017

Setting Sitecore SQL Server Database Permissions

I recently started on a Sitecore 8.2 project and was setting the SQL Server permissions for the SQL user that I had created for Sitecore to use.  The idea here is to provide least-permissions to the user so that it has enough access to work, but not so much access that it is able to do things that it should not be able to. I feel like I spent way too much time looking for the permissions that should be set.  I eventually found what I was looking for in the installation guide for Sitecore! (#rtfm)

In an effort to make sure this information is accessible (read: so that I find it when I forget next time), I've duplicated the content here.

For the "Master", "Web", "Sessions" and "Analytics" databases, select the following permissions:

  • db_datareader
  • db_datawriter
  • public 

For the Core database, select the following permissions:

  • db_datareader
  • db_datawriter
  • public
  • aspnet_Membership_BasicAccess
  • aspnet_Membership_FullAccess
  • aspnet_Membership_ReportingAccess
  • aspnet_Profile_BasicAccess
  • aspnet_Profile_FullAccess
  • aspnet_Profile_ReportingAccess
  • aspnet_Roles_BasicAccess
  • aspnet_Roles_FullAccess
  • aspnet_Roles_ReportingAccess

For all databases:

In addition to assigning the proper roles to your user for the Sitecore databases, you also want to allow the user to execute stored procedures.  For each database, open the "Properties" window, select "Permissions", select your user and select the "Grant" checkbox for the "Execute" permission.

This is all from the 8.2 Update 2 installation guide, which I can't seem to link to due to authentication requirements on http://dev.sitecore.net, but I think the permissions are the same for previous versions of Sitecore as well.