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.