The versions affected are SQL Server 2005, 2008, 2008 R2.
If we grant CONNECT SQL to public server role, any AD account can log in to server.
So we'll have all the AD members on the server without any corresponding login.
This is not documented anywhere, and it's not reproducible on SQL Server 2000 or starting with SQL Server 2012, i.e. if we do the same on 2012, any AD account trying to log in will receive the 18456 error
Login failed for user ...
with the reason
Could not find a login matching the name provided
That's fine and that is expected, but for what reason 2005 - 2008 R2 behave differently?
UPDATE I want also to ask the community about the behavior that is still presented at least on SQL Server 2012: Let's have an AD account that is not mapped to the server, so it cannot access the server because there is no login for it, niether for any Windows Group he is member of. Now I execute the code:
exceute as login = 'myDom\myAcc';
select *
from sys.fn_my_permissions(null, 'server')
The first interesting thing is that I CAN impersonate the login that does not exists. The second thing is this non existing login HAS a server-level permission VIEW ANY DATABASE.
When I was on SQL Server 2008 R2 I thought it's normal, only undocumented, because ALL THE AD PRINCIPALS ARE MEMBERS OF PUBLIC (so it was sufficient to grant CONNECT SQL to public and all the AD members could access the server)
On SQL Server 2012 granting CONNECT SQL to public does not allow to the whole AD to log in, so I thought it was a bug fixed.
But how then can I impersonate smth that does not exist and from what this non existing login inherits a permission?
Or maybe I can ask this way:
It's clear that non existing login inherits his VIEW ANY DATABASE permission from public server role, but why then he does not inherit CONNECT SQL if I grant it to public? Is it consistent behavior? Was it more consistent prior to 2012? Or maybe it was just a bug introduced with SQL Server 2005 and fixed only in the half?