Recently I have installed a fresh new SharePoint 2013 farm based on the October 2013 CU. During the process of defining and fine tuning the way of working together with the customer I found an issue with adding an existing database to a web application. In another post I have described the benefits of creating databases in SQL Server before they are added to a SharePoint web application. When I added an existing database to a web application using Central Administration, it all seemed just fine at first sight.
Conditions of the set up are:
- SQL Server 2012 SP1
- SharePoint Server 2013 with October 2013 CU
- Account permissions and security settings
Special attention to the SharePoint Farm service account, where it has the DBCreator and SecurityAdmin fixed server role on the SQL box.
Although I reproduced the issue on 3 other SharePoint farms at the customer’s site, I wanted to reproduce it on a totally different environment. So, I took one of my own virtual machines. I have tested it with RTM, March 2013 PU and October 2013 CU.
While in SQL Server Management Studio (SSMS) I created databases named PreCreated_Content_RTM, PreCreated_Content_MarchPU and PreCreated_Content_OctoberCU. With the right version of SharePoint (first RTM, then March PU and October CU) I added the existing database to the web application http://demo.octavie.local using Central Administration.
Then I switched back to SSMS and opened the properties of the login SP_Farm and then navigated to the page User Mappings:
It is noticeable that the farm account has not become the owner of the content database when using the October 2013 CU. The log file shows several exceptions, including one mentioning that the role db_owner cannot be altered:
However, when I add the existing database using PowerShell, the farm account does become the owner!
Result:
Looking at the log file again I see the same exceptions in the beginning, but altering the role db_owner works just fine.
Now, what happens when the SharePoint Farm service account does not own a content database? I am still able to create site collections in that content database. I have added regular users with contribute permissions on a team site. Those users are able to add content to lists and libraries. It is still not clear to me what account is used to connect and create/update content in the SQL database. I will dig into that another time. For now, it just doesn’t feel right. What about you? Can you reproduce this issue and can you tell me what can go wrong? Maybe services that run under the SharePoint Farm account, such as OWSTimer?
Update: I have run a SQL Profiler trace for the database PreCreated_Content_OctoberCU. It shows that all actions are executed by two processes: OWSTimer and W3WP using respectively the SharePoint farm administrator account and application pool account. Then I executed some T-SQL statements that showed the database roles and permissions for all database users. None of these accounts have the db_owner role, but do have the role SPDataAccess.
| Role | Principle |
| db_owner | dbo |
| SPDataAccess | MAVENSP_Farm |
| SPDataAccess | MAVENSP_Content |
This SPDataAccess role has the permissions CREATE TABLE, EXECUTE, SELECT, INSERT, UPDATE AND DELETE. This leaves me with the question: Why does the farm administrator account needs to have the db_owner role? For what purposes?
Update 2: Sigh. Just ran into this piece of TechNet content: http://technet.microsoft.com/en-us/library/cc678863.aspx#Section4 Apparantly, the database role SPDataAccess replaces the db_owner role in SharePoint 2013. Once again, TechNet is not consistently up-to-date and secondly not even the SharePoint bits are, I guess. Really confusing I must say.
Summary
When your SharePoint 2013 farm is using the October 2013 CU you might expect issues when you add pre-created content databases to your web application using Central Administration. You are forced to use the PowerShell cmdlet Mount-SPContentDatabase. Using Central Administration or PowerShell ends up with different results, but both seem to work. IMHO, TechNet is not consistently up to date and I guess the SharePoint 2013 bits aren’t consistent as well on this subject.
