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.