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
- 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.
Liked it? Share with others!
Related
This entry was posted on March 27, 2014 at 3:30 AM and is filed under SQL 2014 Learning Series, SQL Server, SQL Server 2014. Tagged: connect any database, learning, permission, SQL, SQL 2014 Learning Series, SQL Server 2014. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
4 Responses to “SQL 2014 Learning Series # 1 – New Permission – Connect any database”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
SQL 2014 Learning Series # 2 – New Permission – Select All User Securables « Help: SQL Server said
[…] « SQL 2014 Learning Series # 1 – New Permission – Connect any database […]
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 😦
blakhani said
None. That’s the new feature in 2014
Server level database permissions | SQL Studies said
[…] 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 […]