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?
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
dbcreatorrole; 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).