is there any reason to explicitly grant the server permission CONNECT SQL?

by marcello miorelli   Last Updated July 27, 2017 12:06 PM

I have been using this script from Kenneth Fisher to get the server permissions.

-- Server Permissions
SELECT Grantee.principal_id AS GranteePrincipalId, AS GranteeName, AS GrantorName, Permission.class_desc, Permission.permission_name, 
   'REVOKE ' + 
       CASE WHEN Permission.class_desc = 'ENDPOINT' THEN NULL 
       WHEN Permission.[state]  = 'W' THEN 'GRANT OPTION FOR ' ELSE '' END + 
       ' ' + Permission.permission_name COLLATE Latin1_General_CI_AS +  
       ' FROM ' + QUOTENAME( COLLATE Latin1_General_CI_AS)  + '; ' AS RevokeScript, 
   CASE WHEN Permission.class_desc = 'ENDPOINT' THEN NULL 
       WHEN Permission.[state]  = 'W' THEN 'GRANT' ELSE Permission.state_desc COLLATE Latin1_General_CI_AS END + 
       ' ' + Permission.permission_name COLLATE Latin1_General_CI_AS +  
       ' TO ' + QUOTENAME( COLLATE Latin1_General_CI_AS)  + ' ' +  
       CASE WHEN Permission.[state]  = 'W' THEN ' WITH GRANT OPTION ' ELSE '' END +  
       ' AS '+ QUOTENAME( COLLATE Latin1_General_CI_AS) + ';' AS GrantScript 
FROM sys.server_permissions Permission 
JOIN sys.server_principals Grantee 
   ON Permission.grantee_principal_id = Grantee.principal_id 
JOIN sys.server_principals Grantor 
   ON Permission.grantor_principal_id = Grantor.principal_id 
WHERE 1=1 
  --AND LIKE '%TAllard%'

In my live systems I get for this user in particular the following permissions (server):

enter image description here

the VIEW ANY DEFINITION permission I explicitly granted but the other permission CONNECT SQL was not explicitly granted. I believe it is automatically generated when you create a login to the server, and a user for that login on any database that live there. that implies a connection.

considering this, is there any specific reason why I should grant the CONNECT SQL at servel level?

Answers 1

When you create a login, it is granted CONNECT SQL.

It has nothing to do with user. When you create a user, it does not automatically create a corresponding login, and CONNECT SQL is a server level permission that can be granted only to a server level principal, i.e. to login or a server role.

The common use of this permission is the following:

Suppose you have a Windows group as a login, in this case all the members of this group can access the server. One day it happens that one member of the group must have no access to server, but it cannot be removed from Windows group that accesses the server. In this case you can DENY CONNECT SQL to that individual account, so the whole group will still access the server and only that account will not

July 27, 2017 12:18 PM

Related Questions

PostgreSQL - automating database grants

Updated July 19, 2018 08:06 AM

Alternate of trigger in SQL server

Updated November 13, 2017 21:06 PM