update-local-sql-server-servername-after-changed-computer-name

Update Local SQL Server ‘ServerName’ after Changed Computer Name

2014-11-25

If you changed your computer name with a SQL Server installed (my version is SQL Server 2012 Express), you will find your installed SQL Server did not change its Servername to new host computer name, which will causes some issues if you have stuff using Windows Authentication access.

You can use the following script to check your current server name:

select @@SERVERNAME

If you see your SQL Server host server name is not your new computer name, you should change:

1: Login your SQL Server using a user with administrator role;

2: In SQLQuery window, use following script:

sp_dropserver '<OldHostName\InstanceName>';     
GO
sp_addserver '<NewHostName\InstanceName>', local; 
(for example: sp_dropserver 'myoldpc\sqlexpress')
GO

for example:

sp_dropserver 'myoldpc\sqlexpress'
GO
sp_dropserver 'mynewpc\sqlexpress'
GO

3: Launch SQL Server Config Manager, restart your SQL Server Instance

Now the new Server Name applied, you can re-connect your SQL Server management studio to double check the new server name.