Logins, SIDs, and Kerberos from First Principles, Part 2: The Vanishing Service Account
Years ago at a previous employer, I wrote a fleet-sweep script that searched every SQL Server instance in the estate for a specific service account, by name and by a known-bad SID. I no longer remember the incident that prompted it, but the script’s shape tells the story: somewhere, a Windows account had been deleted and recreated with the same name, and instances across the fleet disagreed about who that account was. This post recreates that failure from scratch, on purpose, and shows why the most obvious diagnostic tool actively misleads you.
Part 1 established the ground rules: Windows account SIDs are issued by the authority that creates the account, the final component (the RID) is never reused, and SQL Server stores the SID verbatim at CREATE LOGIN time. Everything below was run against SQL Server 2019 (CU32) and SQL Server 2025 (RTM) installed side by side on the same machine, which gives us a genuine two-instance “fleet” sharing one Windows account database.
Act 1: A Healthy Service Account
Create a local Windows account (in a domain this would be an AD account; the mechanics are identical) and grant it a login on both instances:
|
1 2 3 4 5 6 7 8 |
PS> New-LocalUser -Name FleetSvc -Password $pw -Description "Fleet service account" Name Enabled Description ---- ------- ----------- FleetSvc True Fleet service account PS> (Get-LocalUser FleetSvc).SID.Value S-1-5-21-2592426199-2042190713-4020115366-1010 |
RID 1010. Now the logins, on both instances:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE LOGIN [WORKSTATION01\FleetSvc] FROM WINDOWS; SELECT [name] , [sid] , [create_date] FROM [sys].[server_principals] WHERE [name] = N'WORKSTATION01\FleetSvc'; |
|
1 2 3 4 5 6 7 8 9 |
/* SQL Server 2019 */ name sid create_date ---------------------- ---------------------------------------------------------- ----------------------- WORKSTATION01\FleetSvc 0x010500000000000515000000D748859A795BB979A6179EEFF2030000 2026-07-24 10:12:41.303 /* SQL Server 2025 */ name sid create_date ---------------------- ---------------------------------------------------------- ----------------------- WORKSTATION01\FleetSvc 0x010500000000000515000000D748859A795BB979A6179EEFF2030000 2026-07-24 10:12:41.700 |
Identical SIDs, ending in F2030000, which is RID 1010 little-endian, exactly as part 1 showed how to read. Both instances copied the SID from Windows at CREATE LOGIN ... FROM WINDOWS time. Windows remains the authority; SQL Server keeps a cached copy. Hold that thought.
Act 2: The Well-Intentioned Mistake
Somebody cleaning up accounts deletes FleetSvc. Applications break, the mistake is noticed, and the account is recreated, same name, same password, five minutes later. No harm done, right?
|
1 2 3 4 5 |
PS> Remove-LocalUser -Name FleetSvc PS> New-LocalUser -Name FleetSvc -Password $pw -Description "Fleet service account" PS> (Get-LocalUser FleetSvc).SID.Value S-1-5-21-2592426199-2042190713-4020115366-1011 |
RID 1011. The authority never reuses a RID,[3] so the recreated account is, cryptographically speaking, a different principal that happens to wear the same name. Every ACL, every group membership, and every SQL Server login that referenced RID 1010 now points at a ghost.
Act 3: The Diagnostic That Lies
The obvious check is to compare what SQL Server has stored against what SUSER_SID() returns for the account name:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT [sp].[sid] AS [stored_sid] , SUSER_SID(N'WORKSTATION01\FleetSvc') AS [live_sid] , CASE WHEN [sp].[sid] = SUSER_SID(N'WORKSTATION01\FleetSvc') THEN 'OK' ELSE 'MISMATCH' END AS [verdict] FROM [sys].[server_principals] AS [sp] WHERE [sp].[name] = N'WORKSTATION01\FleetSvc'; |
|
1 2 3 |
stored_sid live_sid verdict ---------------------------------------------------------- ---------------------------------------------------------- ------- 0x010500000000000515000000D748859A795BB979A6179EEFF2030000 0x010500000000000515000000D748859A795BB979A6179EEFF2030000 OK |
Both instances report OK. Both are wrong. The account in Windows has RID 1011; both SIDs above end in F2030000, RID 1010.
Here is the gotcha, and it is a big one: when a login with the requested name already exists on the instance, SUSER_SID() answers from sys.server_principals, not from Windows.[2] You asked SQL Server to verify its cached copy, and it checked the cache against itself. The proof: drop the login on the 2025 instance and ask again, so there is no cached entry to answer from:
|
1 2 3 |
DROP LOGIN [WORKSTATION01\FleetSvc]; SELECT SUSER_SID(N'WORKSTATION01\FleetSvc') AS [live_sid_no_login]; |
|
1 2 3 |
live_sid_no_login ---------------------------------------------------------- 0x010500000000000515000000D748859A795BB979A6179EEFF3030000 |
With the login gone, SUSER_SID() finally asks Windows and returns the truth: F3030000, RID 1011. A SID-mismatch diagnostic that relies on SUSER_SID() alone will never fire, because the very thing you are trying to detect (a stale stored SID) suppresses the detection. A trustworthy comparison needs an external source of truth: Get-LocalUser or Get-ADUser from PowerShell, or an LDAP query via OPENROWSET against the directory for domain accounts.
Act 4: Fleet Divergence
Recreate the login on the 2025 instance (simulating “we fixed the broken server”), and now the fleet is split:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
/* run against every instance in the fleet */ SELECT @@SERVERNAME AS [instance] , [name] , [sid] , [create_date] FROM [sys].[server_principals] WHERE [name] = N'WORKSTATION01\FleetSvc' /* known-bad SID from the incident */ OR [sid] = 0x010500000000000515000000D748859A795BB979A6179EEFF2030000; |
|
1 2 3 4 |
instance name sid create_date -------------------- ---------------------- --------------------- ----------------------- WORKSTATION01\SQL2019 WORKSTATION01\FleetSvc 0x0105...EFF2030000 2026-07-24 10:12:41.303 WORKSTATION01\SQL2025 WORKSTATION01\FleetSvc 0x0105...EFF3030000 2026-07-24 10:25:09.560 |
The 2019 instance holds the stale SID (RID 1010) and will refuse the recreated account’s connections with login failures; the 2025 instance holds the current SID (RID 1011) and works fine. Same name everywhere, different identities, and which servers work depends on which ones had their login recreated after the account was. This is why my old fleet-sweep script searched by name and by known-bad SID: names tell you where the account should work, SIDs tell you where it actually will.
Act 5: The Orphan, On Demand
The same mismatch reproduces the classic orphaned-user failure without any account deletion drama. Create a database on the stale-SID instance with a user for FleetSvc, back it up, and restore it on the current-SID instance (the same movement pattern covered in the backup series restore-sequence post):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT [dp].[name] AS [database_user] , [dp].[sid] AS [user_sid] , [sp].[sid] AS [login_sid] , CASE WHEN [sp].[sid] IS NULL THEN 'ORPHANED (no SID match)' WHEN [dp].[sid] = [sp].[sid] THEN 'mapped' END AS [status] FROM [sys].[database_principals] AS [dp] LEFT JOIN [sys].[server_principals] AS [sp] ON [dp].[sid] = [sp].[sid] WHERE [dp].[name] = N'WORKSTATION01\FleetSvc'; |
|
1 2 3 |
database_user user_sid login_sid status ---------------------- ------------------- --------- ------------------------ WORKSTATION01\FleetSvc 0x0105...EFF2030000 NULL ORPHANED (no SID match) |
A login named WORKSTATION01\FleetSvc exists on the restore target. The user named WORKSTATION01\FleetSvc is orphaned anyway, because users map to logins by SID, and the database traveled with RID 1010 while the server holds RID 1011. The fix re-stamps the user’s SID from the login:
|
1 |
ALTER USER [WORKSTATION01\FleetSvc] WITH LOGIN = [WORKSTATION01\FleetSvc]; |
Re-running the diagnostic afterwards shows mapped, with both SIDs ending in F3030000.[1]
What to Take Away
- Deleting and recreating a Windows account creates a new principal. The name is cosmetic; the RID is the identity, and it never comes back.
- Do not trust
SUSER_SID()to validate an existing login. It returns the stored SID whenever the login exists, which is precisely the case you are trying to check. Compare against Windows or AD directly. - Sweep by SID, not just by name. After any account-recreation incident, search the fleet for the known-bad SID; name-based searches show you nothing wrong.
- The fix is mechanical once diagnosed: drop and recreate the login (new SID flows in from Windows), then
ALTER USER ... WITH LOGINfor any database users that came along from older restores.
Next up: part 3 migrates SQL logins between servers with their SIDs and passwords intact, so restored databases arrive with no orphans at all.
Ever been bitten by a recreated account? I would love to hear about it in the comments, or find me on Bluesky or LinkedIn.
References
- Troubleshoot orphaned users (SQL Server) – Microsoft Learn. Detecting and repairing users whose SIDs match no server-level login. ↩
- SUSER_SID (Transact-SQL) – Microsoft Learn. Documents that the function returns the SID of the login when it exists on the instance. ↩
- Security identifiers – Microsoft Learn. RID allocation and why relative identifiers are never reused. ↩