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.

Filed under: Microsoft SQL Server, Security, SQLServerPedia Syndication Tagged: database permissions, microsoft sql server, security, server permissions