What does "Grant Connect on Endpoint as [sa]" do?

Question

I was going through the script generated by my Visual Studio Database project and I found this:

GRANT CONNECT
    ON ENDPOINT::[TSQL Default TCP] TO PUBLIC
    AS [sa];

I don't know what it does, but it seems to grant PUBLIC access as SA (just by how it reads).

Anyone know what this really does? (Clearly it grants some access via an "ENDPOINT". But in plain english what does it do?)

By just reading it, it says to me that anyone connecting via TCP port can run as [sa]. (I hope that is not right, but if it is, why would Visual Studio's DB Project do that?)

Answer 1

To start with, the GRANT is incorrect for one obvious reason: it uses the name [PUBLIC] for the grantee, but it should be [public]. On a case sensitive installation the name will not resolve.

Now about your question: the AS [sa] part is relevant for the action of granting the permission, it does not transfer to the grantee. It reads something like:

I, in the name of [sa], grant the privilege of connecting to the [TSQL Default TCP] endpoint to the members of the [public] group.

Specifically, it does not imply that the grantee (the members of [public]) are to be elevated to [sa]. Granting connect permission is a necessary but not sufficient privileged to connect to a database. The grantee would still require the permission to access the database (ie. to have a user mapped to its login in the database). Also, granting connect permission to the [public] server principal is not equivalent to creating a login for the BUILTIN\Everyone (or BUILTIN\ANONYMOUS LOGIN for the matter...) security principals (or other built-in account)... In other words an NT user that has no login (explicit or implicit via an NT group membership) in SQL Server will still not be able to connect to the instance.

As a side note, running the following query on any brand new installation reveals that the permission to connect to the default T-SQL endpoint is already granted to [public]:

select s.name as grantee, 
    e.name as endpoint,
    p.permission_name as permission,
    p.state_desc as state_desc
from sys.server_permissions p
join sys.server_principals s on s.principal_id = p.grantee_principal_id
join sys.endpoints e on p.major_id = e.endpoint_id
where p.type='CO'