Microsoft has introduced three new permissions namely Connect Any Database, Impersonate Any Login and Select All User Securables in its SQL Server 2014 version. The CONNECT ANY DATABASE permission seemed more interesting to me from a developer perspective as we all have come across situation if we had this permission we could have avoided a sad face:-(. In our daily life we work across different servers and databases but sad reality is that the developer doesn’t has permission to access all databases across every server he works. Classic example is a developer working for marketing team having access only for a Marketing database and may not have access to Finance database. So what happens if he accidentally hits the Finance database? A dialog box opens with a message saying “The database is not accessible” this caused a sad face in me many times. The CONNECT ANY DATABASE permission is the solution for it by which it will allow the developer to connect any database in the server and will not have him access to the underlying DB objects thereby preventing the dialog box.
Let’s try demonstrating it.
I created a SQL Server Authentication login for this demonstration and name it as SqlCastTest. Its default permissions can be viewed by Right Clicking the Login and opening its Properties window under tab Securables. The Effective permissions could be seen like below.
Let’s try opening the database SQLCAST2014 through this login in object explorer and it will pop an error message as follows.
Let’s give CONNECT TO ANY DATABASE permission to our SqlCastTest login by executing the below script
USE [MASTER]
GO
GRANT CONNECT ANY DATABASE TO [SqlCastTest]
GO
Now let’s try connecting to the same database and it will let us connect to it as follows.
To verify our drill let’s check the new permission available at Securables tab in Login,
See the CONNECT ANY DATABASE is effective and associated with our test login like below.Keep in mind, CONNECT ANY DATABASE will not allow access to the DB objects and it’s only a connect permission to the existing and future databases in the server.
Hope it helps:-)
#JunaithHaja