Least-privileged administration in SharePoint 2013: SQL Server Role DBCREATOR

When installing SharePoint you need an account to set up each server in the farm. This setup account also needs access to SQL Server. However, you probably do not use the default Domain Administrator account (and if you do: shame on you!). More and more companies ask for a least-privileged environment. The TechNet articles for both SharePoint 2010 and SharePoint 2013 describe the accounts and services needed for a (vanilla) SharePoint installation and configuration. It gets more complicated when BI services and Kerberos are in the game, but that’s a story for another time.

Setup Account

In this article II ‘d like to focus a bit on the SQL Server role DBCREATOR that the setup account needs to have. Like you do not use the Domain Administrator for the installation of SharePoint, you also do not apply the SQL Server role SYSADMIN to this setup account. It is sufficient to have the roles DBCREATOR and SECURITYADMIN applied.

Like described on TechNet these roles can do the following tasks:

• Dbcreator – Members of the dbcreator fixed server role can create, alter, drop, and restore any database.

• Securityadmin – Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions if they have access to a database. Additionally, they can reset passwords for SQL Server logins.

Let’s take this into practice. In my case I have the following setup account: MAVEN\sp_install

This account is Local Administrator on all SharePoint servers:

 

…and has a SQL Server login with the server roles DBCREATOR and SECURITYADMIN.

Creating databases

In one of my previous posts I talked about the advantages of pre-creating databases for SharePoint. Every database in SQL has some initial values based on the Model database. In my example I have the following default values set in the Model database:

But here’s the thing. When I login with MAVEN\sp_install into SQL Server Management Studio and I create a new database I get the following results:

Not the values I have set in the Model database! Although you are allowed to create databases, you do not have (read) access to the Model database. Hence, the different initial values. Also, when you browse to the SQL Logins and opens the User Mappings of one of the logins, you’ll get a message:

Once again, this is due to not having access to the Model database.

So, how disturbing is this? It depends. In most (large) companies there is someone that is responsible for SQL Server environments (aka the SQL Database Administrator). This DBA can help you with creating databases and configuring more stuff in order to maintain your high-performance SharePoint farm. If you the all-in-one guy (or girl), you have the choice to use a different account for SQL Administration or grant the MAVEN\sp_install account db_datareader permissions on the Model database.

Summary

The setup account for SharePoint installation has a SQL Server login with the server roles DBCREATOR and SECURITYADMIN applied. However, the role DBCREATOR does not have access to the Model database and therefor different initial values are used when creating databases.

Tags: Installation; SQL Server Role