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] GO CREATE USER [MyDomain\MySharepointAccount] FOR LOGIN [MyDomain\MySharepointAccount] ALTER ROLE [db_owner] ADD MEMBER [MyDomain\MySharepointAccount] GRANT ALTER ANY AVAILABILITY GROUP TO [MyDomain\MySharepointAccount]; GRANT CONTROL SERVER TO [MyDomain\MySharepointAccount]; GRANT CREATE ANY DATABASE TO [MyDomain\MySharepointAccount];
Categories: Agent Jobs, Alerts, Automations, Backup & Restore, Big Data, Cluster, Command Prompt, Configuration, Database Mail, Database Mirroring, ELI5, Error Solutions, Failover, Failover Cluster, Featured, Folder System, Forensics & Auditing, High Availability, How To, Installations, Long Scripts, Mirroring, Notification, Powershell, Quick Fix, Scripts, Security, Short Scripts, SQL Stuff, Top, TSQL, Work Arounds