Windows Authentication Users Domain Will not Change when Server Name Changed

in practice, you need to change the server name due to many reasons. There are few challenges you need to face as Database administrator. This is one of them.

After changing the server name, you need to run following stored procedures.

sp_dropserver SERVER2

GO

sp_addserver SERVER3, local

GO

SERVER2 being the old server name and SERVER3 is the new server name.

After this if you can login to the server with SERVER3\Administrator if you have configured SERVER2\Administrator user before. However, if you closely look at there is no user called SERVER3\Administrator instead user is SERVER2\Administrator.

When server name is changed, I believe these windows authentication users also changed. Now you need to run following script to change user names accordingly.

/****** Object: Login [SERVER2\Administrator] Script Date: 04/06/2010 14:51:51 ******/

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'SERVER2\Administrator')

DROP LOGIN [SERVER2\Administrator]

GO

/****** Object: Login [SERVER3\Administrator] Script Date: 04/06/2010 14:51:51 ******/

CREATE LOGIN [SERVER3\Administrator] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

GO