Add-SPShellAdmin SharePoint 2013 and the new SPDataAccess role

Remember using the Add-SPShellAdmin cmdlet to grant you or your favourite colleagues access to SharePoint databases without involving your DBA? In SharePoint 2010, this command gave you db_owner access to the SharePoint database.

Not in SharePoint 2013…

The Add-SPShellAdmin cmdlet does not grant the user membership  to the db_owner role, but instead places the user in a SPDataAccess role.

SPDataAccess

The SP_DATA_ACCESS role replaces the db_owner role in SharePoint 2013.

From Technet:

SP_DATA_ACCESS database role

The SP_DATA_ACCESS role is the default role for database access and should be used for all object model level access to databases. Add the application pool account to this role during upgrade or new deployments.

The SP_DATA_ACCESS role will have the following permissions:

  • Grant EXECUTE or SELECT on all SharePoint stored procedures and functions
  • Grant SELECT on all SharePoint tables
  • Grant EXECUTE on User-defined type where schema is dbo
  • Grant INSERT on AllUserDataJunctions table
  • Grant UPDATE on Sites view
  • Grant UPDATE on UserData view
  • Grant UPDATE on AllUserData table
  • Grant INSERT and DELETE on NameValuePair tables
  • Grant create table permission

Reference: http://technet.microsoft.com/EN-US/library/cc678863.aspx

Leave a Reply

Your email address will not be published. Required fields are marked *