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

What this permission does is explained here as:

The CONNECT permission allows you to connect to the associated instance or database. In and of itself CONNECT SQL/CONNECT grants no other permissions.

This permission is granted automatically when the login or the user is created and for a user to be able to connect, this is necessary.

Rigerta Demiri
Rigerta Demiri
July 27, 2017 11:56 AM

Related Questions

Alternate of trigger in SQL server

Updated November 13, 2017 21:06 PM

Script to backup all databases

Updated January 07, 2017 08:02 AM