Help: SQL Server

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

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

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?

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.