Logins, SIDs, and Kerberos from First Principles, Part 1: What a SID Actually Is
Ask SQL Server who you are and it will answer with a name. Ask it to make a decision about you, though, and it uses something else entirely: a security identifier, or SID. Names are for humans. SIDs are what every permission check, every database user mapping, and every ownership chain actually compares. When logins misbehave, orphaned users, mysterious permission failures after a restore, service accounts that stop authenticating, the root cause is almost always a SID that does not match what something expected.
This is part 1 of a series that builds SQL Server authentication up from first principles: what SIDs are, how they break, how to migrate logins without losing them, and how Kerberos fits in. Later parts cover what happens when a Windows account is dropped and recreated, migrating logins between servers with their SIDs intact, and why your connections silently fall back from Kerberos to NTLM. Everything below was run against SQL Server 2019 (CU32) and cross-checked on SQL Server 2025 (RTM).
Every Server Principal Has One
The sys.server_principals catalog view exposes the SID for every login, role, and mapped account on the instance.[2] A quick census by principal type:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT [name] , [type_desc] , [sid] , DATALENGTH([sid]) AS [sid_length] FROM [sys].[server_principals] ORDER BY [type_desc] , [name]; |
A representative sample of the output:
|
1 2 3 4 5 6 7 8 |
name type_desc sid sid_length -------------------------- ------------- ------------------------------------------------------------------- ---------- sa SQL_LOGIN 0x01 1 public SERVER_ROLE 0x02 1 sid_demo_login SQL_LOGIN 0x0AAAB3B8EDE356439ABBC0028B226BFD 16 WORKSTATION01\FleetSvc WINDOWS_LOGIN 0x010500000000000515000000D748859A795BB979A6179EEFF2030000 28 NT AUTHORITY\SYSTEM WINDOWS_LOGIN 0x010100000000000512000000 12 NT Service\MSSQL$SQL2019 WINDOWS_LOGIN 0x0106000000000005500000002FEC1EFF3F2F0AA68045E689AA40AA0C429F62E0 32 |
Four different lengths, four different stories:
- 1 byte: fixed, well-known principals.
sais0x01on every SQL Server instance on the planet. Server roles get similar single-byte identifiers. - 16 bytes: SQL logins. When you run
CREATE LOGIN ... WITH PASSWORD, SQL Server generates a random 16-byte value (it is a GUID under the covers). Nothing outside this instance knows or cares about it, which becomes very important in part 3. - 28 bytes: Windows accounts and groups. This is a genuine Windows security identifier, issued by the domain controller (or the local machine), and SQL Server stores it verbatim. SQL Server did not invent this value and cannot change it.
- 32 bytes: virtual service accounts (
NT SERVICE\...). These are S-1-5-80 SIDs, deterministically derived from a SHA-1 hash of the service name. The same service name produces the same SID on every machine.[1]
Reading a Windows SID
The string form of a Windows SID looks like this:
|
1 2 3 4 5 6 |
S-1-5-21-2592426199-2042190713-4020115366-1010 | | | \_________________________/ | | | | machine or domain identifier | | | | (48 bits, three 32-bit words)| | | identifier authority (5 = NT) RID: relative identifier | revision (always 1) (this specific account) |
The three middle values identify the issuing authority: a domain, or a standalone machine. Every account that authority creates shares those values. The final component, the relative identifier (RID), distinguishes individual accounts, and the authority hands them out sequentially: the first local account you create gets RID 1000-ish, the next one higher, and so on. A RID is never reused. That single fact drives everything in part 2.
The binary form SQL Server stores is the same data with the 32-bit words serialized little-endian. Take WORKSTATION01\FleetSvc from the output above and carve up the hex:
|
1 2 3 4 5 6 7 |
0x01 05 000000000005 15000000 D748859A 795BB979 A6179EEF F2030000 | | | | \__________________________/ | | | | | machine identifier RID, little-endian: | | | 21 (0x15) 0xF2030000 = 0x03F2 = 1010 | | authority 5, big-endian | sub-authority count (5) revision 1 |
The last four bytes, F2030000, read backwards as 0x000003F2 = decimal 1010: the RID. Once you know to look at the tail of the binary SID, you can spot consecutively created accounts at a glance, and more usefully, you can spot when two SIDs that “should” match differ only in the RID.
Names Are Just a Lookup
Two built-in functions convert between the name and the SID:[3]
|
1 2 3 |
SELECT SUSER_SID(N'sid_demo_login') AS [sid_from_name]; SELECT SUSER_SNAME(SUSER_SID(N'sid_demo_login')) AS [name_from_sid]; |
|
1 2 3 4 5 6 7 |
sid_from_name ---------------------------------- 0x0AAAB3B8EDE356439ABBC0028B226BFD name_from_sid -------------- sid_demo_login |
The round trip works, and it is tempting to conclude the name and the SID are interchangeable. They are not. The name is a label pinned to the SID, and the two can drift apart. SUSER_SID in particular has a subtle behavior with Windows accounts that will get its own treatment in part 2, because it has misled a lot of troubleshooting sessions.
SQL Login SIDs Are Random, Every Time
Here is the demonstration that sets up the rest of the series. Create a SQL login, capture its SID, drop it, and recreate it with the identical name and password:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DECLARE @sid1 varbinary(85) = SUSER_SID(N'sid_demo_login'); DROP LOGIN [sid_demo_login]; CREATE LOGIN [sid_demo_login] WITH PASSWORD = N'Demo!Passw0rd#2026' , CHECK_POLICY = OFF; DECLARE @sid2 varbinary(85) = SUSER_SID(N'sid_demo_login'); SELECT @sid1 AS [original_sid] , @sid2 AS [recreated_sid] , CASE WHEN @sid1 = @sid2 THEN 'YES' ELSE 'NO' END AS [same_sid]; |
|
1 2 3 |
original_sid recreated_sid same_sid ---------------------------------- ---------------------------------- -------- 0x0AAAB3B8EDE356439ABBC0028B226BFD 0x960C7119E369E740AED6F128A80460B5 NO |
Same name, same password, completely different identity. Every database user, every permission grant, every ownership record that referenced the original SID now points at a value that no longer exists. To SQL Server, the recreated login is a stranger who happens to have the same name, and the same thing happens with Windows accounts when someone deletes and recreates them in Active Directory, which is exactly where part 2 picks up.
Why This Layer Matters
Everything above sounds academic until you map it onto the failures it explains:
- Orphaned database users are a SID in a restored database that matches no SID at the server level, even when the names line up perfectly.
- Login migrations that silently break everything happen when the new server generates fresh random SIDs instead of carrying the old ones over.
- “We recreated the account and now nothing works” is a new RID wearing an old name.
Each of those gets a dedicated post with reproducible demos. Next up: part 2 deletes a service account and recreates it, then watches two SQL Server instances disagree about who it is.
Questions about a SID mystery of your own? I would love to hear about it in the comments, or find me on Bluesky or LinkedIn.
References
- Security identifiers – Microsoft Learn. The structure of Windows SIDs, well-known SIDs, and the S-1-5-80 service SID scheme. ↩
- sys.server_principals (Transact-SQL) – Microsoft Learn. The catalog view exposing the SID for every server-level principal. ↩
- SUSER_SID (Transact-SQL) – Microsoft Learn. Name-to-SID conversion, including its behavior when the login already exists on the instance. ↩