The CONNECT permission

4

January 27, 2016 by Kenneth Fisher

The CONNECT permission exists at the instance and database levels. Note: as of SQL 2012 it is CONNECT SQL at the instance level, but prior to that it was just CONNECT. It does basically the same thing in both cases. The CONNECT permission allows you to connect to the associated instance or database. In and of itself CONNECT SQL/CONNECT grants no other permissions. You can’t query any table or view, execute any function or stored procedure etc. Now frequently once connected you will find you have some basic permissions but that is because either you have permissions granted via an AD group (for example) or because those permissions are granted to the Public role. Like it sounds everyone is effectively a member of the Public role and shares in it’s permissions.

Now typically the CONNECT permission is granted automatically when the server principal (LOGIN) or the database principal (USER) is created. Every now and again for whatever reason I’ll have a user try to connect and fail and it turns out the CONNECT permissions was not granted.


You’ll see an error like this if the CONNECT SQL permission is missing to the Instance.

Cannot connect to (local)\sql2014cs.

Login failed for user ‘MyLogin’. (Microsoft SQL Server, Error: 18456)


This if the user has CONNECT SQL to the instance but is missing CONNECT to their default database.

Cannot connect to (local)\sql2014cs.

Cannot open user default database. Login failed.
Login failed for user ‘MyLogin’. (Microsoft SQL Server, Error: 4064)


And last but not least if you try to USE a database where you don’t have CONNECT permission.

Msg 916, Level 14, State 1, Line 31
The server principal “MyLogin” is not able to access the database “Test2” under the current security context.


Now for the ever present example :).

I try to connect to the instance using the SQL Server login MyLogin and get back that first error above.

I run my sp_SrvPermissions stored procedure on the principal:

EXEC sp_SrvPermissions 'MyLogin'

And got back the following:

CONNECT permission

You’ll see that it exists, and it’s active and everything. So why can’t it connect? Well if you look you’ll see that there is no CONNECT permission. Easy enough to grant it.

GRANT CONNECT SQL TO MyLogin

And we run sp_SrvPermissions again and get:

CONNECT permission2
This time we see the CONNECT SQL permission. And when we try to connect again we get in with no problems.

4 thoughts on “The CONNECT permission

  1. DataSic says:

    Nice write-up. Regarding CONNECT – one of my favorite interview questions is ‘How would you disable database user’.

  2. Mark D Powell says:

    Why isn’t the version of SQL Server used in the demonstration identified? The stored procedure sp_svrpermissions does not appear to exist in my 2012 system.

    • It wouldn’t. It’s a SP that I wrote. You can find it in the menu under free scripts. Along with sp_dbpermissions. It should work for anything 2005 and up. (has some issues with contained DBs though)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,655 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: