MS SQL Server

Change @@SERVERNAME

Applies to: Microsoft SQL Server 2012

I encountered this need when I got the following error trying to add a user to my local SQL Server:

SQL Server - Invalid Urn filter on server level: filter must be empty or server attribute must be equal with the true server name

In my case, the problem was that the VM I was using had installed SQL Server with the hostname of the base image - which I had changed when (as instructed) when I started using it. This could be observed by running

SELECT @@SERVERNAME  AS pre_change_name,
Serverproperty('Servername') AS current_server_name

Using sp_addserver to set the correct hostname and then restarting SQL Server resolved my issue:

EXEC sp_dropserver 'OLD_SERVER_NAME'
EXEC sp_addserver 'NEW_SERVER_NAME', 'local'

C:\> NET STOP MSSQLSERVER
C:\> NET START MSSQLSERVER

Reference:

• SQL Server – Change @@servername - sp_dropserver and sp_addserver