Dec 01, 2014

Today, I’d like to have a look at a new permission added to SQL Server 2014 that gives us a couple of interesting possibilities. So, hang in there while we have a quick squiz! Okay, so you know that it’s a reasonable thing that there are restrictions on databases so that people can’t just do a select on any ol’ table in any database, right? I shouldn’t be allowed, for example, to open up a finance database in my company and check out everyone’s salaries nor, presumably, should just about anyone else. Only those that are permitted to see information of that nature, confidential if you like, should have access. Excellent, we’re on the same page. Alright then, how about administrators? People who should have access to the SQL Server instance? This group of hard-working, no doubt extraordinarily attractive, people have work to do, backing up databases, defragmenting indexes, updating statistics, you know, the day-to-day stuff that has to get done? So, SQL Server 2014 has introduced CONNECT ANY DATABASE as a means to give database scope permissions without giving away any access to the objects within. This means that you can also grant access to all databases, but only for very specific tasks, like selecting all data, updating, deleting, etc. To illustrate what this means for you, I’ve got a local SQL instance with some databases. Firstly, I’ll create a user with a password using the UI:

With this, I can log into SQL Server but can’t access any objects. If I attempt to expand any of the databases that I can see I’ll receive an error: Now, let’s give me some permissions:

Okay, with this, I can expand the databases now, but little else:

Great! I can see them but can’t really administer them. So what’s the point? Well, the real magic lies in combining CONNECT ANY DATABASE with other permissions. Let’s try something: Okay, combined with the previous permission, I now have the ability to monitor the performance of the server without touching any data. For example: So now I have unfettered access to the DMV’s combined with no data access. Perfect! We have a simple server-level permission that gives us access to all current and future databases in the form of CONNECT ANY DATABASE. On its own it doesn’t really do a lot, but when we combine it with other permissions, cool things like being able to monitor databases without access to the underlying data become easy. You no longer need to create users in all databases for a login or to assign specific database-scope permissions just to make sure that someone tasked with monitoring a database can do their job. This is excellent news. And don’t go haring off implementing this in your production environment before you’ve tested it thoroughly. But I don’t need to tell you that, do I?