SQL Server - How to prevent public connections?

As per your description, it seems to me that CONNECT SQL (or perhaps CONNECT) permissions has been assigned to the public server role.


What TSQL statement could show us the problematic configuration?

Any login connects to server thanks to CONNECT SQL granted to it directly or to it role/windows group at the server level. You should examine these roles/groups, and to sO them you should impersonate your suspected login by running this code:

execute as login = 'your_suspected_login';

select *
from sys.login_token; -- shows all groups/server roles

select *
from sys.login_token t
     join sys.server_permissions p
        on t.principal_id = p.grantee_principal_id 
           and p.permission_name = 'CONNECT SQL' -- shows what grants him CONNECT SQL

revert;

The first part of the code shows you all the roles/groups that login is member of, the second shows you which role/group has CONNECT SQL. So until your login is member of this last group/role he will connect to your server.

Tags:

Sql Server