When granting access to logins or users to access a SQL Server instance or database, it is important that the principle of least privilege (PoLP) is followed. This helps to ensure that only those authorized users are able to perform certain actions, which is important for the security and integrity of our data.
In practice I find that many sysadmins rarely follow this pracfice, often because of two reasons. First, the practice of setting a variety of permissions is cumbersome and the tendency to take the easiest (or laziest) path is common. The second problem is that the permission sets and necessary permissions for various actions are often poorly understood by most people. This leads towards the common "grant nothing" or "grant everything" approaches. Ineed, often the "sa" account (or other sysadmin privileged login) is often used in applications because this avoids any permission errors. Likewise, db_datareader and db_datawriter are often granted to every user to avoid setting more granular permissions and following the PoLP.
In an attempt to reverse this trend, I am providing a series of articles on SQL Server permissions that help sysadmins create and use roles that contain granular levels of permissions for certain jobs. This particular article examines the permissions needed for the DROP DATABASE command. This is a command that many developers may need to use on their development instances, and it does not require sysadmin privileges.
The Permissions Required
In Books Online for DROP DATABASE, we can scroll down to the Permissions section and see that for an on-premises SQL Server instance or a Parallel Data Warehouse, we need these permissions:
- CONTROL permission on the database
- Or ALTER ANY DATABASE
- Or the db_owner role in the database.
For Azure SQL Database the permissions are different.
These permissions are included in various other roles. For example, if a user is a member of the sysadmin or dbcreator server roles, they can also drop databases. A member of the serveradmin role, however, cannot drop a database.
Let's check that these permissions are correct.
CONTROL on a Database
The CONTROL permission can be granted at the server level or the database level. To remove a database, let's first check this permission.
I have a database, EmptyFileTest (used for another experiment) that I no longer need. As you can see in Fig. 1, there aren't any users in here.
Fig. 1 - Security for the EmptyFileTest database
Now I'll connect to my instance as JoeDev, as basic user. As you can see (Fig. 2), this user has no server permissions, or in Fig 3, no database mapping here.
Fig. 2 - Server Roles for JoeDev
Fig. 3 - Database mapping for JoeDev
Let's try to drop the database.
Fig. 4 - Another drop attempt
That makes perfect sense. This login has no rights on the server, or in the database. Let's change that. We'll add the CONTROL permission for the user. To do this, we need to add our login as a user and grant the permission from within the database. Here's the script that does this:
USE EmptyFileTest GO CREATE USER JoeDev FOR LOGIN JoeDev GO GRANT CONTROL ON DATABASE::EmptyFileTest TO JoeDev GO
Once that's done, let's try again. We can see in Fig. 5 that this works.
Fig. 5 - Dropping the database
This works. With a user in the database, or rather, a user that was in the database, and the CONTROL permission, my login can drop the database.
Testing ALTER ANY DATABASE
The next permission to try is the server level, ALTER ANY DATABASE. If we look in Books Online for server permissions, we can see that this permission is implied by the CONTROL SERVER permission. We'll test both of these.
First, let's grant CONTROL SERVER to our login. You can see this in Fig. 6.
Fig. 6 - Granting server permissions
Once I do this, I can drop the database. That's expected. This looks the same as it did above, so no new image. Instead, let's remove this permission and grant ALTER ANY DATABASE.
REVOKE CONTROL SERVER FROM JoeDev GO GRANT ALTER ANY DATABASE TO JoeDev GO
Once we do this, once again, I find I can drop the database as JoeDev.
Fig. 7 - Tired of switching Windows, just dropping the database with impersonation
There is no ALTER DATABASE xx permission, so we won't test anything more here. All combinations of trying to grant a permission similar to Fig. 8 also fail.
Fig. 8 - No ALTER DATABASE permission
Being in the db_owner role
The db_owner role is similar to sysadmin, but for a database. In Books Online, this is the description:
Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.
This is what we want, and a quick test shows this in Fig. 9.
Fig. 9 - Adding db_owner and dropping the database.
This makes perfect sense. The owner of the database should be able to drop it. None of the other database roles allow this. The security and backup operators are most likely, but those don't allow dropping the database.
Other Choices
What about other server roles? We have a number that could allow these permissions. They certainly allow other rights, but kniwing which ones allow dropping databases is important. Let'stry a few.
Serveradmin
This is considered the second highest permission set by some. This role can shutodwn the server and change the configuration. However, as Fig. 10 shows, this isn't sufficient.
Fig. 10 Dropping a database as Serveradmin
dbcreator
The only other role that likely cna do anything is the dbcreator role. This is a role that should allow any of these actions on any database.
- CREATE
- ALTER
- DROP
- RESTORE
This role should be able to drop databases. Indeed, if we test this, we can see that it works in Fig. 11.
Fig. 11 - Testing dbcreator role.
If we use sp_srvrolepermission, we can see that ALTER DATABASE and DROP DATABASE permissions exist for this role (Fig. 12).
Fig. 12 - Permissions for dbcreator
In fact, if I we connect with the Dedciated Administrator Connection and then look for all the roles that have the DROP DATABASE permission, we'll find this is just sysadmin and dbcreator. This is shown in Fig. 13.
Fig 13. - Roles with the DROP DATABASE permission
Note: The sys.role_permissions table isn't visible without a DAC connection.
Conclusion
The permissions required for dropping a database are fairly limited, with only two server roles granting them. There are limited permissions in the database as well, with only the db_owner role allowing this.
If you are working with developers, you can create dbcreator to allow them to manage all the databases on an instance. If you don't want to allow them to delete any database, but rather specific ones, you should limit permissions. In this case, granting certain individuals the dbcreator role in specific databases may be the best choice.