Logins, SIDs, and Kerberos from First Principles, Part 5: Windows Groups and the Invisible Members
Every post in this series so far has dealt with principals you can see: a login row in sys.server_principals with a name and a SID. Windows groups break that comfortable assumption. Grant a group a login[2] and every member of that group can connect, yet none of them appear in any SQL Server catalog view. The people connecting to your instance are, from the catalog’s point of view, invisible.
That is by design, and mostly a good design: group-based access means onboarding and offboarding happen in Active Directory instead of on every instance. But it changes how you answer basic security questions. “Who can access this server?” is no longer a catalog query. This post shows what the catalog can and cannot tell you, and the one tool that bridges the gap. Demos ran against SQL Server 2019 (CU32) with local Windows accounts and groups; domain groups behave identically.
The Group Gets a Login, the Members Do Not
Building on the FleetSvc account from part 2, create a local group containing it, and grant the group a login:
|
1 2 |
PS> New-LocalGroup -Name FleetOps PS> Add-LocalGroupMember -Group FleetOps -Member FleetSvc |
|
1 2 3 4 5 6 7 8 9 10 |
CREATE LOGIN [WORKSTATION01\FleetOps] FROM WINDOWS; SELECT [name] , [type_desc] , [sid] FROM [sys].[server_principals] WHERE [name] LIKE N'WORKSTATION01\Fleet%'; |
|
1 2 3 4 |
name type_desc sid ---------------------- ------------- ---------------------------------------------------------- WORKSTATION01\FleetSvc WINDOWS_LOGIN 0x010500000000000515000000D748859A795BB979A6179EEFF2030000 WORKSTATION01\FleetOps WINDOWS_GROUP 0x010500000000000515000000D748859A795BB979A6179EEFF4030000 |
The group has its own SID (RID 1012, reading the tail as part 1 showed), because groups are principals in Windows just like users. Notice what is not here: nothing connects FleetSvc to FleetOps. The membership lives in Windows, and SQL Server never copies it. If FleetSvc had no individual login of its own, it could still connect through the group while appearing in no catalog view at all.
xp_logininfo: Asking Windows at Runtime
The bridge is xp_logininfo, which queries Windows (or the domain) live rather than reading any SQL Server catalog.[1] With @option = 'members', it expands a group:
|
1 2 3 |
EXEC [sys].[xp_logininfo] @acctname = N'WORKSTATION01\FleetOps' , @option = 'members'; |
|
1 2 3 |
account name type privilege mapped login name permission path ---------------------- ---- --------- ---------------------- ---------------------- WORKSTATION01\FleetSvc user user WORKSTATION01\FleetSvc WORKSTATION01\FleetOps |
There is the invisible member, with the permission path column showing which group grants the access. The other direction is often more useful in an incident: given an account, how does it get in? @option = 'all' lists every path:
|
1 2 3 |
EXEC [sys].[xp_logininfo] @acctname = N'WORKSTATION01\FleetSvc' , @option = 'all'; |
|
1 2 3 4 |
account name type privilege mapped login name permission path ---------------------- ---- --------- ---------------------- ---------------------- WORKSTATION01\FleetSvc user user WORKSTATION01\FleetSvc NULL WORKSTATION01\FleetSvc user user WORKSTATION01\FleetSvc WORKSTATION01\FleetOps |
Two rows: FleetSvc can connect via its own direct login (permission path NULL) and via the FleetOps group. This matters for offboarding: dropping the direct login does not cut access while the group path remains, and vice versa. An account’s effective access is the union of every row this returns.
Auditing an Entire Instance
To answer “who can actually connect here?”, walk every Windows group login and expand each one. The skeleton:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
DECLARE @group sysname; DECLARE [group_cursor] CURSOR LOCAL FAST_FORWARD FOR SELECT [name] FROM [sys].[server_principals] WHERE [type_desc] = N'WINDOWS_GROUP' AND [is_disabled] = 0; OPEN [group_cursor]; FETCH NEXT FROM [group_cursor] INTO @group; WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY EXEC [sys].[xp_logininfo] @acctname = @group, @option = 'members'; END TRY BEGIN CATCH /* group deleted from Windows/AD: the part 2 scenario, server-side */ PRINT CONCAT(@group, N': ', ERROR_MESSAGE()); END CATCH; FETCH NEXT FROM [group_cursor] INTO @group; END; CLOSE [group_cursor]; DEALLOCATE [group_cursor]; |
The TRY/CATCH is load-bearing. xp_logininfo raises an error for any group that no longer exists in the directory, and on an instance of any age, some will not: group logins go stale exactly the way part 2’s deleted account did. Those errors are findings, not noise; each one is a login that grants access to nobody and should be dropped.
A few practical cautions for real estates:
- Nested groups expand one level at a time. A member of type
groupin the output needs its own expansion; production-grade audits recurse (and must handle cycles). - Cross-domain lookups can be slow or fail depending on trust configuration; run audits with a timeout and capture partial results.
- The result is point-in-time. Group membership changes in AD take effect at the member’s next login token issue, and your audit snapshot ages immediately. Part 8 complements this with continuous capture of who actually connects.
Groups and Database Users
One more wrinkle: a member connecting via a group gets the group’s database user context. CREATE USER [WORKSTATION01\FleetOps] in a database gives every member access there, and objects they create default to schemas in unexpected ways (a member without a direct user gets an implicit schema named after their own login when creating objects, a classic source of schema sprawl). Auditing database access has the same invisible-member problem at a second scope, and the same tool answers it.
What to Take Away
- Group logins mean the catalog does not know who can connect.
sys.server_principalslists doors, not people. xp_logininfois the bridge:'members'expands a group,'all'shows every permission path for an account. Effective access is the union of all paths.- Offboarding requires severing every path. A dropped direct login means nothing while a group path remains.
- Errors from stale groups are audit findings. A group login pointing at a deleted directory group is dead weight with a name on it.
Next up: part 6 moves from who you are to how you prove it: Kerberos, SPNs, and the silent NTLM fallback hiding in almost every estate.
How does your shop balance group-based versus direct logins? I would love to hear about it in the comments, or find me on Bluesky or LinkedIn.
References
- xp_logininfo (Transact-SQL) – Microsoft Learn. Options, output columns, and permission-path semantics. ↩
- Create a login – Microsoft Learn. Windows principals (users and groups) as SQL Server logins. ↩