The Unexpected Side Effect of DENY CONTROL

Introduction

Say, you want to give a user access to a database, but you want to prevent them getting a higher level permission like CONTROL DATABASE, even if they might be a member of a role that has that permission. In DENY vs. REVOKE - Part 2 - Hierarchies of Principals we saw that a DENY always trumps a GRANT even if the GRANT happened to a role and the DENY to a role member. So the first inclination you might have, is to just DENY CONTROLL ON DATABASE to that user of yours.

DENY CONTROL Example

Let's try out if that DENY CONTROL DATABASE works out. First we need a user and a table in an example database:

This creates a user with an associated login and the table we need. However, it also creates a second login that we will use in a later example.

The next step is to GRANT SELECT on that table to TestUser1 and also DENY CONTROL on the database:

This is odd. We setup the correct permission and used EXECUTE AS to execute the SELECT statement as TestUser1. However we are getting an error saying the TestLogin1 cannot access the database. Any login that has an associated user in a database can access that database, I thought, but yet it seems this is not correct here.

Let's try something else. Any login has access to the master database by default. There are no permissions that need to be set up for this and no users that need to be created in master. Now, having access to master does not mean being able to do everything in that database, but any login will be able to run some simple queries like this one:

Now, we did not create a user for TestLogin2 in the master database, so we cannot DENY CONTROL on the database. However, we also don't want TestUser2 to take over control of the entire server, so let's deal with that first by using DENY CONTROL SERVER:

Even though we did not DENY anything in master, TestLogin2 is suddenly not able to connect to it anymore. What happened?

Denying All Inclusive Permissions

In DENY vs. REVOKE - Part 4 - Hierarchies of Privileges I told you that a DENY anywhere in a hierarchy of privileges also trumps any GRANT in that same hierarchy.

CONTROL DATABASE is a permission that includes any other database permission. So if we DENY CONTROL on a database we inclusively deny all other privileges on that database too, particularly the CONNECT privilege. The same holds true for the CONTROL SERVER privilege. As the highest privilege in SQL Server it includes every other privilege. So denying CONTROL SERVER effectively denies everything, period.

Denying Just CONTROL

So, how can I achieve to deny just the actions that require CONTROL and that are not covered within any other permission? Sadly, I am not aware of any way to do this. The only option you have is to pay careful attention to not grant any permission to the principal that you did not indeed intend to include. You can consider using techniques like auditing to help you with this, but you cannot enforce it.

Summary

Executing a DENY CONTROL can have unexpected side effects. Because of the hierarchical way denies work in SQL Server and because the CONTROL privilege is always on the highest level of the privilege hierarchy, denying CONTROLL effectively denies every single permission on the securable. For databases or even the server itself, that includes in particular the CONNECT privilege, making any access to that entire resource impossible.