none
Difference Betwenn SpDataAccess and PShellAdmin RRS feed

  • Question

  • I normally use this script to give users access to run power shell scripts, in 2010 they would get SPShellAdmin and DBO in 2013 they seem to get SpDataAccess and SpShellAdmin, any difference between the two?

    Add-PSSnapin Microsoft.SharePoint.PowerShell;
    $username = Read-Host "Enter username";
    Get-SPContentDatabase | ForEach-Object {Add-SPShellAdmin -UserName $username -database $_.Id}

    Monday, November 17, 2014 8:15 PM

Answers

  • Hi,

    The SharePoint_Shell_Access role gives you access to the content databases and the configuration database, and permission to execute the stored procedures.

    In order to use PowerShell, an administrator must be assigned the SharePoint_Shell_Access role on any databases against which PowerShell will be used. For example, to perform tasks that read or manipulate data in the configuration database, an administrator must have the SharePoint_Shell_Access role for the configuration database. Likewise, to work with a specific site collection, the admin must have the SharePoint_Shell_Access role for the appropriate content database. More information: http://sharepointpromag.com/sharepoint/tis-privilege-powershell.

    The db_owner fixed database role has the CONTROL DATABASE permission. Members of the db_securityadmin fixed database role can modify role membership and manage permissions.  More information: http://msdn.microsoft.com/en-us/library/ms189121(v=sql.105).aspx.

    Per my knowledge, except that you mentioned, all application pool identity should have db_owner fixed database role for the associated services database. And the farm account also need to have db_owner role.

    Best Regards,

    Wendy

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Wendy Li
    TechNet Community Support

    • Marked as answer by Lindali Friday, November 28, 2014 8:39 AM
    Thursday, November 20, 2014 5:56 AM
    Moderator

All replies

  • Hi themush,

    According to your description, my understanding is that you want to know the difference between db_owner role and SPDataAccess role.

    The SP_DATA_ACCESS role replaces the db_owner role in SharePoint 2013.

    The SP_DATA_ACCESS role is the default role for database access and should be used for all object model level access to databases.

    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

    More information, please refer to the link:

    http://www.jeremytaylor.net/2013/10/14/add-spshelladmin-sharepoint-2013-new-spdataaccess-role/

    Best Regards,

    Wendy

    Forum Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Wendy Li
    TechNet Community Support

    Tuesday, November 18, 2014 8:22 AM
    Moderator
  • I want to know the Different between SP_DATA_ACCESS and SharePoint_Shell_Access.

    Is there any harm in accounts having DBO in addition to SP_DATA_ACCESS or is it just redundant.

    I still see the farm account getting DBO on content DB's is that still supposed to happen?

    is there anything any account needs DBO for?

    Technet shows this

    Setup user account

    The user account that is used to run:

    If you run Windows PowerShell cmdlets that affect a database, this account must be a member of the db_owner fixed database role for the database.

    • Setup on each server computer

    • SharePoint Products Configuration Wizard

    • The Psconfig command-line tool

    • The Stsadm command-line tool

    Tuesday, November 18, 2014 1:03 PM
  • Hi,

    The SharePoint_Shell_Access role gives you access to the content databases and the configuration database, and permission to execute the stored procedures.

    In order to use PowerShell, an administrator must be assigned the SharePoint_Shell_Access role on any databases against which PowerShell will be used. For example, to perform tasks that read or manipulate data in the configuration database, an administrator must have the SharePoint_Shell_Access role for the configuration database. Likewise, to work with a specific site collection, the admin must have the SharePoint_Shell_Access role for the appropriate content database. More information: http://sharepointpromag.com/sharepoint/tis-privilege-powershell.

    The db_owner fixed database role has the CONTROL DATABASE permission. Members of the db_securityadmin fixed database role can modify role membership and manage permissions.  More information: http://msdn.microsoft.com/en-us/library/ms189121(v=sql.105).aspx.

    Per my knowledge, except that you mentioned, all application pool identity should have db_owner fixed database role for the associated services database. And the farm account also need to have db_owner role.

    Best Regards,

    Wendy

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Wendy Li
    TechNet Community Support

    • Marked as answer by Lindali Friday, November 28, 2014 8:39 AM
    Thursday, November 20, 2014 5:56 AM
    Moderator
  • Hi,

    Do you have any update for this issue?

    Best Regards,

    Wendy


    Wendy Li
    TechNet Community Support

    Monday, November 24, 2014 9:48 AM
    Moderator