Agent Jobs

Apply Permissions For Sharepoint Configurations In AlwaysOn

Lets say you have a brand new SQL AlwaysOn environment for SQL 2012 or SQL 2014 and now the Sharepoint Admins are connecting to the Listener name so they can install and configure Sharepoint. The Sharepoint configuration process may run into a few permissions errors whenever they try to create their databases. So you ‘the DBA’ will check to ensure that all the appropriate permissions are set on each Node in the Cluster for the Sharepoint Administrator account which in this example is MyDomainMySharepointAccount. You notice that it’s set as the Sysadmin for both the OS and SQL Server and has already been added to the database owner permissions.
(Basically it’s been added to Master dbo, and the local administrators group on the Server it’s self, and it’s already added to the Sysadmin role under each SQL Server instance.)

However; you still get issues under ‘Specify Configuration Database Settings’ for the Sharepoint configuraiton process. They see this error:

Cannot connect to database master at SQL Server at MyAvailabilityGroupName. The database might not exist or the current user does not have permission to connect to it.

Well… in usual Microsoft fashion just because it’s been added to the appropriate roles for the OS, and SQL Server it doesn’t mean the setup process from another Microsoft application will choose to check those to validate. In this case you’ll need to grant specific permissions in the Securables so the Sharepoint setup process will validate that the appropriate permissions have been set.

You can do this in 2 ways of course… Add the securables through the SSMS client GUI, or simply run the following logic. Note: This will need to be run on all Nodes in the Cluster configuration.

use [master]

CREATE USER [MyDomain\MySharepointAccount] FOR LOGIN [MyDomain\MySharepointAccount]
ALTER ROLE [db_owner] ADD MEMBER [MyDomain\MySharepointAccount]
GRANT CONTROL SERVER TO [MyDomain\MySharepointAccount];
GRANT CREATE ANY DATABASE TO [MyDomain\MySharepointAccount];


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s