Add users as SharePoint Shell Admin for specific site collection or content database

Recently we received a concern from one of our team members that they are not able to run PowerShell scripts against a specific web application or site collection. They were trying to Backup the site collection and using Backup-SPSite cmdlet.

However they were not able to do so and got the following error:

Backup-SPSite : Cannot open database "<database_name>" requested by the login.

The login failed.

Login failed for the user '<domain\username>'.

At line:1 char:14

We ran the following command to add the user as PS Shell Admin

Add-SPShellAdmin -UserName domain\username

However, even after doing so they were getting login failed error when trying to run PowerShell commands. The problem was occurring because as the error stated, the user's login did not have SharePoint_Shell_Access rights on the database. You can easily check this by checking the database security properties (from SQL management studio) and check the permissions of the account. If the account is not listed under Database -> Security -> Users, then it is obvious that the account does not have any permission on that content database.

When we run "Add-SPShellAdmin -UserName domain\username", it only added the user account under SharePoint Config database with SharePoint_Shell_Access rights. User's account was not added on any of the content databases with the Shell rights. That’s why when the user tried to backup a site collection, they were getting login failed error. For more details on what the Add-SPShellAdmin cmdlet, please refer to this article - https://technet.microsoft.com/en-us/library/ff607596.aspx

So what we had to do was to specifically grant SharePoint_Shell_Access rights by passing on the content database name like in the example below:

 Get-SPDatabase | ?{$_.Name -eq "WSS_Content"} | Add-SPShellAdmin -Username DOMAIN\Username

This solved the problem. Also, in case the user again complains for login failed error, you need to take a look at the site where he is trying to run the command and make sure that the account does have SharePoint_Shell_Access permissions on that database that is holding that site collection. If not, run Add-SPShellAdmin cmdlet by specifying the database. As always, its best practice to have the least permissions and review the SharePoint Shell Access rights using the command - Get-SPShellAdmin and remove any user accounts that are not supposed to be there.