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):
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?
When you create a
login, it is granted
It has nothing to do with
When you create a user, it does not automatically create a corresponding login,
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