A DBA may provide review, but the Database Engineer can create their own databases to improve velocity A non-DBA PowerUser, such as a Database Engineer, who you trust to create new databases. An automation service account, such as a DevOps pipeline, which creates databases.Let’s think about some different user stories where you might want to grant permission to create databases: On the other hand, the CreateDbTest login is NOT able to drop that database. You’ll notice that the DbCreatorTest login is able to drop the DontDropMe database that it doesn’t have permission to. GRANT CREATE ANY DATABASE TO CreateDbTest That code would look something like this (I’m using EXECUTE AS syntax to make these permission tests easy):ĬREATE LOGIN DbCreatorTest WITH PASSWORD = 'Notorious_RBG' ĪLTER SERVER ROLE dbcreator ADD MEMBER DbCreatorTest ĬREATE LOGIN CreateDbTest WITH PASSWORD = 'Notorious_RBG' Using the new logins, try to drop some database that the logins don’t have permission to.Add one login to the dbcreator role Grant the other CREATE ANY DATABASE.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? Not only can dbcreator create databases, it can also alter databases. OK, that seems fine, right? Do you need dbcreator?Įxcept… that’s a little bit more than what “database creator” would seem to imply. Members of the dbcreator fixed server role can create, alter, drop, and restore any database.īut 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: Microsoft docs defines the dbcreator role as:
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |