• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

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?
0
enrique_aeo
Asked:
enrique_aeo
  • 5
  • 5
4 Solutions
 
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now