The CONNECT permission
11January 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:
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:
This time we see the CONNECT SQL permission. And when we try to connect again we get in with no problems.
SQL Studies 
Nice write-up. Regarding CONNECT – one of my favorite interview questions is ‘How would you disable database user’.
Thanks 🙂 Yea that question has a lot of great depth to it. I could see it becoming a fun conversation.
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)
I have a security requirement to remove the GRANT option from the CONNECT SQL permissions…but when I REVOKE (just the) GRANT option it removes the permission. Any thoughts?
I assume you want to remove the “with grant option” that lets someone grant this permission to someone else correct? If so:
REVOKE GRANT OPTION FOR CREATE TABLE TO [userid] CASCADE
This will not only remove the “WITH GRANT OPTION” for the userid but will also revoke the permission from anyone they granted it to.
If on the other hand you are trying to just remove the “GRANT” entry this means they will no longer have that permission. “GRANT” in this case does not mean that they can grant to someone else, it means they were granted the permission.
Hi,
Thanks for this write up and the SrvPermissions SP. I’ve identified a couple of users with it that HAVE got CONNECT SQL granted but cannot connect to the instance. Any ideas why this would happen? Google hasn’t turned up very much at all (apart from this site!)
The most common case of this I see is mistaking an AD login for a SQL login. So I guess my first questions are: Which are they, and how are they trying to connect?
[…] no need to worry about the new user that was created. It has absolutely no permissions. Not even CONNECT that is usually added by […]
[…] The CONNECT permission SQL Studies […]
I had the same issue and was resolved r running this command
USE master;
GRANT connect SQL TO [XXXX];
GO