I've done some testing with what happens to Windows accounts which have been renamed after being added to SQL Server. I'm going to demonstrate using local user accounts and groups though the same behaviour will be apparent in Active Directory.
My VM is named W08R2S14 and here's the original user cleverly named OriginalName and member of OriginalGroup.
![][1]
![][2]
And the SIDs.
PsGetsid.exe OriginalName PsGetSid v1.44 - Translates SIDs to names and vice versa Copyright (C) 1999-2008 Mark Russinovich Sysinternals - www.sysinternals.com SID for W08R2S14\OriginalName: S-1-5-21-2640196809-3402802471-4060526964-1009 PsGetsid.exe OriginalGroup PsGetSid v1.44 - Translates SIDs to names and vice versa Copyright (C) 1999-2008 Mark Russinovich Sysinternals - www.sysinternals.com SID for W08R2S14\OriginalGroup: S-1-5-21-2640196809-3402802471-4060526964-1010
First I create a login within SQL Server for OriginalName and confirm its SID translation using the Outer Apply method [shown in my previous post][3].
Create Login [W08R2S14\OriginalName] From Windows
Select sid_string
From sys.server_principals
-- Snip
Where name = 'W08R2S14\OriginalName'
S-1-5-21-2640196809-3402802471-4060526964-1009
I created a SQL Server Profiler trace to capture the Security Audit - Login Audit event with all fields and write them to master.dbo.LoginTrace on the same instance. In the real world we set up traces to write these to rolling files, which are queried remotely and stored on a centralised server for alerting and analysis.
![][4]
I'll test the login and verify it shows up in the trace, along with the addition of the SID translation routine which I've tweaked because of the different data types involved. I've split up the output so you can see everything that is possible to capture.
runas.exe /user:OriginalName "sqlcmd -S .\SQL2014 -E -Q \"Select 1\""
Select *
From master.dbo.LoginTrace sp
Outer Apply (
Select 'S-' +
Convert(Varchar, Convert(Int, Substring(sp.LoginSid, 1, 1))) +
'-' +
Convert(Varchar, Convert(Int, Substring(sp.LoginSid, 3, 6))) +
'-' +
Convert(Varchar, Convert(Bigint, Convert(Varbinary, Reverse(Substring(sp.LoginSid, 9, 4))))) +
Case
When Datalength(sp.LoginSid) > 13
Then '-' +
Convert(Varchar, Convert(Bigint, Convert(Varbinary, Reverse(Substring(sp.LoginSid, 13, 4)))))
Else ''
End +
Case
When Datalength(sp.LoginSid) > 17
Then '-' +
Convert(Varchar, Convert(Bigint, Convert(Varbinary, Reverse(Substring(sp.LoginSid, 17, 4)))))
Else ''
End +
Case
When Datalength(sp.LoginSid) > 21
Then '-' +
Convert(Varchar, Convert(Bigint, Convert(Varbinary, Reverse(Substring(sp.LoginSid, 21, 4)))))
Else ''
End +
Case
When Datalength(sp.LoginSid) > 25
Then '-' +
Convert(Varchar, Convert(Bigint, Convert(Varbinary, Reverse(Substring(sp.LoginSid, 25, 4)))))
Else ''
End +
Case
When Datalength(sp.LoginSid) > 29
Then '-' +
Convert(Varchar, Convert(Bigint, Convert(Varbinary, Reverse(Substring(sp.LoginSid, 29, 4)))))
Else ''
End
As LoginSidString
) sp2
Where ApplicationName = 'SQLCMD'
And LoginSidString = 'S-1-5-21-2640196809-3402802471-4060526964-1009'
RowNumber | EventClass | ApplicationName | BinaryData | ClientProcessID | DatabaseID | DatabaseName |
---|---|---|---|---|---|---|
116 | 14 | SQLCMD | 0x2000002838F4010000000000 | 880 | 1 | master |
EventSequence | EventSubClass | GroupID | HostName | IntegerData | IsSystem | LoginName |
---|---|---|---|---|---|---|
195 | 1 | 1 | W08R2S14 | 4096 | NULL | W08R2S14\OriginalName |
LoginSid | NTDomainName | NTUserName | RequestID | SPID |
---|---|---|---|---|
0x010500000000000515000000C9345E9D27A5D2CA74B906F2F1030000 | W08R2S14 | OriginalName | 0 | 51 |
ServerName | SessionLoginName | StartTime | Success | TextData | Type | LoginSidString | |||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
W08R2S14\SQL2014 | NULL | 2015-06-03 21:44:06.833 | 1 | -- network protocol: LPC set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed | 1 | S-1-5-21-2640196809-3402802471-4060526964-1009
</tr>
</tbody>
</table>
Now the fun begins. Using lusrmgr.msc I've renamed OriginalName to NewName. Naturally this does not change the name of the login stored in SQL Server.
## Q. What happens to the login trace after we change the login name in Windows?
Nothing.
It remains exactly the same. Despite logging in with a different login name only the old login name is displayed in each of the trace fields, because they're all filled in by SQL Server. Authentication is done on the SID.
Weirdly however the ERRORLOG will show a different story. I've tested here using the NewName login, and then re-tested it when the login is disabled. Both show NewName instead of OriginalName.
``` sql
Exec master.sys.xp_readerrorlog 0, 1, N'W08R2S14'
```
Msg 15401, Level 16, State 1, Line 5
Windows NT user or group 'W08R2S14\OriginalName' not found. Check the name again.
Msg 15098, Level 16, State 1, Line 8
The name change cannot be performed because the SID of the new name does not match the old SID of the principal.
**I would not go and just fix up logins where you find them. It could have unintended consequences if you're using impersonation, Resource Governor, or have schemas tied into the login names.**
For my next test I disable the login, add the group login, and run a login test again.
``` sql
Drop Login [W08R2S14\OriginalName]
Create Login [W08R2S14\OriginalGroup] From Windows
```
## Q. What will be in the trace after disabling the login and adding a group login?
A login failure from W08R2S14\NewName. __Even though the group exists, it cannot login when the more specific login is disabled.__
## Q. What will be in the trace after removing the login and adding a group login?
The names in the trace change to reflect the account name (NewName) but otherwise nothing different.
- LoginName = W08R2S14\NewName
- NtUserName = NewName
- LoginSid_String = S-1-5-21-2640196809-3402802471-4060526964-1009
Even though we're authenticating against a group, or possibly multiple groups, only the direct login name information and SID will be reported. If you check in your server principals you will find nothing relevant to tell you how this account just logged in. If you're optimistic you may use this to try to work it out:
``` sql
Exec master.sys.xp_logininfo 'W08R2S14\NewName', 'all'
```
|