If you arrived here then you’ve already seen that pesky “…cannot use the special principal ‘dbo’” error whenever you try to update, or edit permissions for the Sharepoint Config Database. OMG annoying right??!!
Rather than giving you a long drawn-out verbose description of the Microsoft Security Architecture between Sharepoint, Powershell, and SQL Server, why don’t I just tell you a quick fix that might work for you.
You’ll need to remove the existing Database Owner of your Sharepoint Configuration Database then update the permissions afterwords. Just as you normally would… Nothing special.
In this scenario I have a Sharepoint 2013 configuration on an environment with SQL Server 2014 using AlwaysOn. I’m using an example domain called ‘MyDomain’, and a Sharepoint Service Account called MyDomainSP_Admin. The Sharepoint Config database is SharePoint2013_Config.
Here’s the logic I used to quickly update the permissions for DB_Owner, Sharepoint_Shell_Access, and SPDataAccess. I’m doing all three for consistency in this example even though I’m well aware of the permissions hierarchy in this situation.
use master; set nocount on exec [Sharepoint2013_Config]..sp_changedbowner 'sa'; use [Sharepoint2013_Config]; create user [MyDomainsp_admin] for login [MyDomainsp_admin] alter role [db_owner] add member [MyDomainsp_admin] alter role [sharepoint_shell_access] add member [MyDomainsp_admin] alter role [spdataaccess] add member [MyDomainsp_admin] go
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, How To, Installations, Long Scripts, Management Studio, Mirroring, Notification, Powershell, Quick Fix, Scripts, Security, Short Scripts, SQL Stuff, Top, TSQL, Work Arounds