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.
