sql server 2014 - Security Enhancements - CONNECT ANY DATABASE Permission

Hi experts,
I do not understand what new:
A new server level permission. Grant CONNECT ANY DATABASE to a login that must connect to all databases that currently exist and to any new databases that might be created in future. Does not grant any permission in any database beyond connect. Combine with SELECT ALL USER SECURABLES or VIEW SERVER STATE to allow an auditing process to view all data or all database states on the instance of SQL Server.

where is the improvement? could not do with sql server 2012 or 2008?
It would be helpful for an explanation with T-SQL?
enrique_aeoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DBAduck - Ben MillerPrincipal ConsultantCommented:
This permission did not exist in SQL 2012 or 2008.  This is a new permission that you can grant to a login that allows that login to connect TO any database. If this permissions is granted the login will still not have ACCESS to the database, but can connect to it.  Which really means that the login can see the database but cannot see it's content.

GRANT CONNECT ANY DATABASE TO [loginname]
0
enrique_aeoAuthor Commented:
Might you help me with T-SQL code to make the test
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
If you create a login that is a regular user and don't add that user to a database.

-- With an Admin User
CREATE LOGIN mylogin WITH PASSWORD='Password1'

-- Create a database
CREATE DATABASE testdb

-- Now login to the instance with the mylogin login
-- Then try to use the testdb
USE testdb

-- It will throw an error
-- Now go back to the original Admin connection and grant permissions
GRANT CONNECT ANY DATABASE mylogin

-- Now go back to the query window that is logged in as mylogin
USE testdb
 -- It should succeed now that the permission of CONNECT ANY DATABASE is there.
-- You won't be able to select anything, but you can connect.

Open in new window


That is what this permission allows.
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

enrique_aeoAuthor Commented:
Hi experts

i have this error
GRANT CONNECT ANY DATABASE mylogin

My version of sqlserver is
Microsoft SQL Server 2014 - 12.0.2000.8 (Intel X86)
      Feb 20 2014 19:20:46
      Copyright (c) Microsoft Corporation
      Express Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
0
enrique_aeoAuthor Commented:
GRANT CONNECT ANY DATABASE mylogin

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'mylogin'.
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
If you look at my code it has the CREATE LOGIN statement so that mylogin exists.
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
Sorry, I reread what you said.  I had an error in my script.

GRANT CONNECT ANY DATABASE TO mylogin

Open in new window

0
enrique_aeoAuthor Commented:
Thank you very much, and I run, but that cases could be useful this new feature?
0
enrique_aeoAuthor Commented:
DBAduck - Ben Miller
why?

Which really means that the login can see the database but cannot see it's content.
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
Well, this is a security enhancement to allow someone to use a database without being a user of the database, which means they get public access to it.  If you had a process that was automated to get information out of the system tables related to the database, with this permission you do not have to create a user to let them get to the database. It is very limited, but at least they could see the metadata for the database without being a user.

It is not always useful to everyone, but it is useful in some cases.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.