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: 'CONNECT ANY DATABASE' in SQL Server 2014

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: 'CONNECT ANY DATABASE' in SQL Server 2014Now, let’s give me some permissions:

sql-2014-connect-db-3 Okay, with this, I can expand the databases now, but little else: 'CONNECT ANY DATABASE' in SQL Server 2014

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: 'CONNECT ANY DATABASE' in SQL Server 2014Okay, combined with the previous permission, I now have the ability to monitor the performance of the server without touching any data. For example: 'CONNECT ANY DATABASE' in SQL Server 2014So 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?

How do your Excel skills stack up?   

Test Now  

About the Author:

Adam Keats  

With over 25 years of real-world IT experience Adam is one of New Horizons’ most senior Database and Software Development trainers. Starting out as a physics teacher Adam displayed exceptional mathematical and technical capabilities early on in his career. He went on to work in a variety of programming and technical management roles within several government agencies including the Department of Defence and Maritime Patrol. In 1998 Adam found his true calling, gaining MCT status and thus beginning his technical training career specialising in SQL Server administration, development, BI, and .NET development. Since then he has worked for several training organisations and found a home at New Horizons where he is now our resident Database and Development specialist. Throughout his tenure at New Horizons, Adam has assisted over 500 students in their endeavours to improve their skills, knowledge, and to achieve industry certifications.