3

My question is very similar to this question Changing Windows Domain UserName but the original question doesn't explicitly answer my slight variation.

If I change a windows users username (if they get married for instance) will my Sql Server login which I created for the old username be updated to reflect the change or will I have to create a new login for the updated windows user?

Thanks Ben

4 Answers 4

5

If the login is tied to the domain account, it should be fine because at the basic level it depends on the SID of the user and not the actual username.

If the new username doesn't work, you may have to reset the token cache with this command:

DBCC FREESYSTEMCACHE('USERSTORE_TOKENPERM');
1
  • @hyppt, I can't confirm this as the correct answer as our IT department rather helpfully won't let developers have a test account but I'll take your word for it and remove my acceptance if it turns out you're bluffing :)
    – BenCr
    Mar 23, 2011 at 12:46
1

The permissions should continue to work properly, but the username displayed in SQL Server will be the old name in some places until you manually update it.

While the drop/create method will work, and apparently creating a duplicate sql login with same SID appears to work for some here, I recommend just updating the existing login record in SQL Server (tested in SQL 2012).

ALTER LOGIN [DomainName\OldUserName] WITH NAME = [DomainName\NewUserName];

This will cleanly change the existing system login record to reflect the correct new username for that domain user (SID).

0

It actually matches on SID, not name. So you'll be OK.

Notes:

  • It's a Windows login you mean. A SQL Server login is not associated with a Windows user.
  • Best practice is to use Windows groups for permissions, not individual WIndows users
5
  • are you sure? I'm talking about server level logins, if you look at a server in management studio under the Security folder there is a Logins leaf so I referd to them as Sql Server logins and windows users. Also I'm using application roles for defining permissions but you don't know enough about my applications requirements to say if using windows groups or logins will suit best. However, thanks for your answer.
    – BenCr
    Mar 23, 2011 at 12:44
  • @BenCR: I'm 100% sure that a "SQL Server login" is not related to Windows logins msdn.microsoft.com/en-us/library/ms181127.aspx
    – gbn
    Mar 23, 2011 at 12:49
  • Ok, please point out to me the exact line which you think is incorrect.
    – BenCr
    Mar 23, 2011 at 12:51
  • A "SQL Server login" is CREATE LOGIN...;. A Windows login is set up in SQL Server as CREATE LOGIN... FROM WINDOWS;. That is, "SQL Server login" is wrong when talking about changing a Windows user name. They are unrelated and are not linked in any way.
    – gbn
    Mar 23, 2011 at 12:54
  • Ahh, I see, so your point is that it's a login to sql server that is called a "windows login" because it's associated with a windows domain account. However thats not what that link you've just provided says, under the section "SQL Server-level principal" it has the name "SQL Server Login".
    – BenCr
    Mar 23, 2011 at 13:07
0

You can easily check they have the same SID after rename by using the below script and check if you get the same SID before and after. Assuming you renamed Domain\LoginA to Domain\LoginB :

SELECT  sid
FROM    sys.server_principals
WHERE   name IN (N'Domain\LoginA')

and

SELECT  sid 
FROM    sys.server_principals 
WHERE   name IN (N'Domain\LoginB')

should give you same SID which proves @Hyppy's point.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .