Site icon Andy M Mallon – AM²

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:

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:

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:

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).

Exit mobile version