Introduction

There are many scenarios where we may want to provide an administrator or service account with the ability to perform routine tasks on a server and its databases, but without having access to the underlying data.  Many professions have restrictions over what employees are allowed to view, such as in hospitals, where HIPAA greatly influences the flow of information.  SQL Server 2014 introduced CONNECT ANY DATABASE as one way to provide database-level permissions without giving any access to the objects within.  This also allows for scenarios where access is granted to all databases, but only for specific tasks, such as selecting all data, updating, deleting, etc…

Scenario 1: Deny Access to Sensitive Data

To illustrate what this new permission can do for you, we will use a local SQL instance with a handful of test databases.  First, I will create a user for myself using the UI, providing nothing more than a password:

With these permissions, I can log into SQL Server, but have no way to access any objects.  We can see databases here, but if we attempt to expand them, we’ll receive a permissions error:

Now let’s provide CONNECT ANY DATABASE permissions to this new user:

GRANT CONNECT ANY DATABASE TO EdPollack
GO

Reconnect to SQL Server and we can now expand a database without getting an access error.  We cannot see anything further, though.  Tables, stored procedures, views, and other user objects are invisible.  Some system objects are visible, but are still inaccessible:

With CONNECT ANY DATABASE, we will be able to view all databases created in the future in addition to databases that already exist.  This seems like an odd way to receive permissions, as we can see every database in the instance, but cannot administer them.  The true value of CONNECT ANY DATABASE is when it is combined with other permissions.  One common use is our problem above: we want access to administer a server, but should not be able to access any sensitive data.  To accomplish this, we can add an additional server permission to this user:

GRANT VIEW SERVER STATE TO EdPollack
GO

With this additional permission, we can now monitor performance without touching any customer data.  For example, we could view all server sessions by using:

SELECT * FROM sys.dm_exec_sessions

We could also view database file usage with:

SELECT * FROM sys.dm_db_file_space_usage

Despite our ability to view system data pertaining to the server or a database, we still cannot view or alter any data on our SQL instance.  These permissions combined will provide us with the perfect combination for monitoring software, which requires unfettered access to DMVs, but does not need any data access.

Scenario 2: Allow Access to All Data

Another permissions combination that can be very useful here would be to take CONNECT ANY DATABASE and combine it with SELECT ALL USER SECURABLES:

DENY VIEW SERVER STATE TO EdPollack
GO
GRANT SELECT ALL USER SECURABLES TO EdPollack
GO

We now have a scenario where we have removed our server-level access from before, but have added data access instead.  SELECT ALL USER SECURABLES will grant a login SELECT permissions on all objects that they have access to.  In this case, CONNECT ANY DATABASE has provided access to all databases on the instance, and therefore we have SELECT permissions on all of them.

I can select data against my test database without a problem:

SELECT
       *
 FROM restaurant.dbo.ingredients

But, when I try to make changes, SQL Server reminds me that I can only look, but not touch:

UPDATE dbo.ingredients
 SET cost = 0.75
 WHERE ingredient_name = 'Tomato Sauce'

Msg 229, Level 14, State 5, Line 11

The UPDATE permission was denied on the object 'ingredients', database 'restaurant', schema 'dbo'.

Lastly, this combination of permissions will allow SELECT access to all databases created in the future automatically, without any need to assign users to logins or make any additional security changes.

Having read-only access to all data on our instance can be an excellent option for processes that copy, report, aggregate, or otherwise collect data.  These permissions will also ensure that user will have no administrative privileges or the ability to modify data.

Conclusion

CONNECT ANY DATABASE is a simple server-level permission that provides access to all current and future databases.  On its own, there is no further functionality provided, but when combined with other permissions, can allow very important business security needs to be met with ease.  Combined with VIEW SERVER STATE, a login can now monitor server and database metrics via a host of dynamic management views.  In conjunction with SELECT ALL USER SECURABLES, a login can view data in all databases (read-only).

With the addition of CONNECT ANY DATABASE, security scenarios that used to be cumbersome to implement are now simple.  There’s no longer any need to create users in all databases for a login or to assign specific database-level permissions to ensure that a service account or monitor can do its job correctly.  As always, test thoroughly in your development environment before making security changes in production, and of course, enjoy!