Hi All,

Greetings for the day 🙂 LIFE IS BEAUTIFUL 🙂

Today new issue and resolution 🙂

Background / Details :

• We were doing migration from On-Prem to On-Prem. From SharePoint 2013 to SharePoint 2016
• I am the local administrator, farm administrator on new SharePoint 2016 server
• I could access the sites / central admin from the server properly
• We were using Content DB attachment method for migration
• So as one of the step is to download the farm solution from existing environment – SharePoint 2013 and deploy on new environment – SharePoint 2016
  • We have already PowerShell script available to download the farm solutions from SharePoint servers please have a look once –
    • SharePoint OnPremises – PowerShell script to download farm solutions – .wsp files – requires while migration and
    • MOSS / SharePoint 2007 – PowerShell script to download all farm solutions – requires while migration
• So we started PowerShell and we started deploying solutions on new environment – SP 2016. Here we are getting an error. (Tried to run PowerShell by “Run as Administrator” as well but no luck 🙁 )
• Tried Get-SPFarm CMDLET and same error

Error / Issue :

Get-SPFarm : Can not access the local farm. Verify that local farm is properly configured, currently available, and that you have the appropriate permission to a database before you try again.

Cause of an error / Why this error :

• This issue is because current use dosent have “SharePoint_Shell_Access” role on “SharePoint_Config” database
• The farm administrator (not to be confused with the farm account) does not automatically have access to the content dbs.

What is “SharePoint_Shell_Access role ?

• The secure SharePoint_SHELL_ACCESS database role on the configuration database replaces the need to add an administration account as a db_owner on the configuration database
• By default, the setup account is assigned to the SharePoint_SHELL_ACCESS database role. We can use a PowerShell command to grant or remove memberships to this role.
• Setup assigns the SharePoint_SHELL_ACCESS role to the following databases:
  • The SharePoint_Config database (the configuration database).
  • One or more of the SharePoint Content databases. This database is configurable by using the PowerShell command that manages membership and the object that is assigned to this role.
• Members of the SharePoint_SHELL_ACCESS role have the execute permission for all stored procedures for the database.
• Members of this role have the read and write permissions on all of the database tables.
• If we are planning to use an account to run PowerShell cmdlets on a database, so it must have a db_owner database role or SharePoint_SHELL_ACCESS database role.
• There is no need to assign a db_owner database role for an account if it already has a SharePoint_SHELL_ACCESS database role.

Solution :

• In order to use Windows PowerShell for SharePoint Products, a user must be a member of the SharePoint_Shell_Access role on the configuration database and a member of the WSS_ADMIN_WPG local group on the computer where SharePoint Products is installed.

Prerequisites to add the user to “SharePoint_Shell_Admin” role :

• Must have membership in the securityadmin fixed server role on the SQL Server instance
• Membership in the db_owner fixed database role
• Local administrative permission on the local computer.

PowerShell CMDLET to add the user to “SharePoint_Shell_Admin” role : Add-SPShellAdmin

Syntax :

Add-SPShellAdmin
   [-UserName] <String>
   [-AssignmentCollection <SPAssignmentCollection>]
   [-Confirm]
   [-Database <SPDatabasePipeBind>]
   [-WhatIf]
   [<CommonParameters>]

Examples : 

Add-SPShellAdmin -UserName <domain name /username> => adds a new user named username to the SharePoint_Shell_Access role in the farm configuration database only, and also ensures the user is added to the WSS_Admin_WPG local group on each server in the farm

What happens behind the scene when we assign SharePoint_Shell_Access role to user :

• The user is added to the WSS_Admin_WPG group on all Web servers
• If the target database does not have a SharePoint_Shell_Access role, the role is automatically created
• On successfully execution of this cmdlet – the user specified with the UserName parameter will have the SPDataAccess role, if it exists, or db_owner role, if the SPDataAccess role does not exist, on the affected databases

Some more details :

• This cmdlet is only to be used with a database that uses Windows authentication
• There is no need to use this cmdlet for databases that use SQL authentication
• If we specify only the user, the user is added to the role for the farm configuration database
• If we use the database parameter, the user is added to the role on the farm configuration database, the Central Administration content database, and the specified database.

Thanks for reading 🙂 HAVE A GREAT TIME AHEAD 🙂