Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,050,824 hits
  • Select GETDATE()

    March 2014
    M T W T F S S

SQL 2014 Learning Series # 1 – New Permission – Connect any database

Posted by blakhani on March 27, 2014

Finally, I was able to get the RTM bits of SQL Server 2014 and installed it on my laptop. Since there are lots of new things, I thought of sharing this with community. I will write them under “SQL 2014 Learning Series”

First thing first.. Here is the @@version for SQL 2014 on my laptop.

Microsoft SQL Server 2014 – 12.0.2000.8 (X64)
    Feb 20 2014 20:04:26
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)


After installing SQL Server 2014, I wanted to check what’s newly added in security layer of SQL Server. So I compared the permission available under server level and found three new permissions in SQL Server 2014


My default instance is SQL 2012 and I can see 31 permission whereas SQL 2014 has 34 permissions. These permissions are listed under Server properties >  permissions tab.


Here is the list


In this blog, we would see use of “Connect any database” . Let me create a SQL login Balmukund on my SQL Instance. Here is the command I have used

USE [master]
CREATE LOGIN [Balmukund] WITH PASSWORD=N'@Very$tr0ngP@$$w0rd'


Now, If I login with the account and try to expand user database, I will get the error.


This is what we have seen in earlier version of SQL as well. Unless we add user to the database, it would not be possible to expand database.

Let’s give new permission to that login.

use [master]

Once permission is given, Balmukund should be able to expand database. But wait.. can he see all objects as well?


Balmukund is unable to see the objects. This is also mentioned in books online “Does not grant any permission in any database beyond connect”

You may ask… what’s the use? All we are avoiding is just the error message? Well, there have been ask from customers to provide “Read-Only” or “Auditor” role for SQL Server. Combining this with other new permission can help in achieving that. The cool thing about this permission is that it will also allow logins to connect to databases that would be created in the future.

In next blog, I will show you demo about other new permissions.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Advertisement

    4 Responses to “SQL 2014 Learning Series # 1 – New Permission – Connect any database”

    1. […] « SQL 2014 Learning Series # 1 – New Permission – Connect any database […]

    2. SQLServer said

      May i know the equivalent permissions in lower versions (SQL 2012/2008 R2… ).

      Already granted “VIEW ANY DATABASE,VIEW ANY DEFINITION and VIEW SERVER STATE” permissions, but its not fixed 😦

    3. […] SQL 2014 Learning Series 1: CONNECT ANY DATABASE SQL 2014 Learning Series 2: SELECT ALL USER SECURABLES New SQL Server 2014 Permissions: CONNECT ANY DATABASE Is it safe to grant ADMINISTER BULK OPERATIONS […]

    Leave a Reply

    Fill in your details below or click an icon to log in: Logo

    You are commenting using your account. Log Out /  Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out /  Change )

    Connecting to %s

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    %d bloggers like this: