0

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

-- Server Permissions
SELECT Grantee.principal_id AS GranteePrincipalId, Grantee.name AS GranteeName, 
   Grantor.name AS GrantorName, Permission.class_desc, Permission.permission_name, 
   Permission.state_desc,  
   '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(Grantee.name 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(Grantee.name COLLATE Latin1_General_CI_AS)  + ' ' +  
       CASE WHEN Permission.[state]  = 'W' THEN ' WITH GRANT OPTION ' ELSE '' END +  
       ' AS '+ QUOTENAME(Grantor.name 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 Grantee.name 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?

3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.