What the heck is the dbcreator role?
Tags: sql, sqlserver, security
Published: 2020-01-01 15:10:24

---
title: What the heck is the dbcreator role?
published: true
date: 2019-12-19 12:30:14 UTC
tags: sql, sqlserver, security
canonical_url: https://am2.co/2019/12/what-the-heck-is-the-dbcreator-role/
---

[![](http://am2.co/wp-content/uploads/ksenia-makagonova-HP9nSjikXq0-unsplash-1024x683.jpg)](https://www.youtube.com/watch?v=7UbY3lmCSb0)

[Microsoft docs](https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-ver15#fixed-server-level-roles) defines the dbcreator role as:

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

But what specific permissions are actually included in that role? If we scroll down in the docs just a little, we’ll see a diagram that tells us that `dbcreator` grants two permissions:

- `ALTER ANY DATABASE`
- `CREATE ANY DATABASE`

OK, that seems fine, right?

## Do you need dbcreator?

Except… that’s a little bit more than what “database creator” would seem to imply. Not only can `dbcreator` _create_ databases, it can also _alter_ databases. If you are granting `dbcreator` to some user, do want them to be able to drop _ANY_ database? Including your DBA database, application databases, etc? Maybe it would be better to grant the more granular `CREATE DATABASE` permission?

Let’s take a close look at this:

- Create two logins
- Add one login to the `dbcreator` role; Grant the other `CREATE ANY DATABASE` 
- Using the new logins, try to drop some database that the logins don’t have permission to.

That code would look something like this (I’m using [`EXECUTE AS` syntax](http://am2.co/2019/12/testing-as-another-user-without-their-password/) to make these permission tests easy):

```sql
CREATE LOGIN DbCreatorTest WITH PASSWORD = 'Notorious_RBG';
ALTER SERVER ROLE dbcreator ADD MEMBER DbCreatorTest;
GO
CREATE LOGIN CreateDbTest WITH PASSWORD = 'Notorious_RBG';
GRANT CREATE ANY DATABASE TO CreateDbTest;
GO
 
CREATE DATABASE DontDropMe;
 
EXECUTE AS LOGIN = 'DbCreatorTest';
    DROP DATABASE DontDropMe;
REVERT;
 
 
CREATE DATABASE DontDropMe;
 
EXECUTE AS LOGIN = 'CreateDbTest';
    DROP DATABASE DontDropMe;
REVERT;
```

You’ll notice that the `DbCreatorTest` login is able to drop the `DontDropMe` database that it doesn’t have permission to. On the other hand, the `CreateDbTest` login is _NOT_ able to drop that database.

Let’s think about some different user stories where you might want to grant permission to create databases:

- An automation service account, such as a [DevOps pipeline](https://www.red-gate.com/simple-talk/sysadmin/devops/introduction-to-devops-the-application-delivery-pipeline/), which creates databases
- A non-DBA PowerUser, such as a Database Engineer, who you trust to create new databases. A DBA may provide review, but the Database Engineer can create their own databases to improve velocity
- A third-party application, which creates and drops databases for staging imports & exports, or for creating historical cold-storage archives.

In all these cases, the process/user is empowered to create and drop their own databases–but oughtn’t be able to drop other databases. In fact–allowing automation to drop _other_ databases is a potentially dangerous scenario. A bug could result in dropping a production database, resulting in downtime and likely data loss.

I can hear someone saying, “But the third party application needs to be able to drop it’s databases after they are done with them–they are essentially temporary databases. Hence, it needs the `ALTER ANY DATABASE` permission, as well.” Specifically, I can hear that third-party software vendor telling me this.

Alas, it is not necessary. If we only grant permission to create the database, the login which creates the database will be the owner of the database. Because it is the database owner, it will be able to do _ANYTHING_ do that database, including dropping that database (and only that database).

The post [What the heck is the dbcreator role?](https://am2.co/2019/12/what-the-heck-is-the-dbcreator-role/) appeared first on [Andy M Mallon - AM²](https://am2.co).