Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

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

image

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.

image

Here is the list

  • CONNECT ANY DATABASE
  • IMPERSONATE ANY LOGIN
  • SELECT ALL USER SECURABLES.

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]
GO
CREATE LOGIN [Balmukund] WITH PASSWORD=N'@Very$tr0ngP@$$w0rd'
GO

 

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

image

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]
GO
GRANT CONNECT ANY DATABASE TO [Balmukund]
GO

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

image

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
  • Advertisements

    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:

    WordPress.com Logo

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

    Twitter picture

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

    Facebook photo

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

    Google+ photo

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

    Connecting to %s

     
    %d bloggers like this: