What the heck is the dbcreator role?

Microsoft docs 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 to make these permission tests easy):

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, 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? appeared first on Andy M Mallon - AM².