You own a personal computer, and you want to learn about SQL Server. Then, you probably installed the SQL Server Developer Edition, which is free. But, there will be times when you may decide to change the computer name. And, because you have already installed SQL Server, you will find there are a few things in the SQL Server configuration that will need to be corrected.
The first sign of something wrong!
In my case, I renamed my computer name from ‘DESKTOP-K6AKFTA‘ to ‘MAXTLAPTOP1‘ with SQL Server previously installed. Then, while executing an SSIS SQL job, the job failed to execute giving me the following error:
Keep in mind, the User-id was added to the SQL Server security logins group with the previous computer name. Here is where you’ll find that something is not right.
Fixing the Computer Name for the User-Id
Use the SQL Server Object Explorer and navigate to the “Security\Logins” folder. Here you can find the local user with the wrong machine name.
To correct the User-Id, just right-click and select “Rename” to change the computer name.
image
Next, right-click in the User-Id, select “Properties“, and then go to “Securables“. Here you’ll find the SQL Server Instance Name is wrong.
image
This needs to be fixed!
Check the current SQL Server Configuration Server Name
To verify the SQL Server configuration system name use the “@@SERVERNAME” function by executing the following T-SQL script:
## - Execute the following T-SQL command: -- -- Get SQL Server first time "Installed" Servername with Instance Name: -- Note: Assuming this is run after the system has been renamed. -- Select @@SERVERNAME CurrentSvrNameWithSqlInstanceName; --Output: CurrentSvrNameWithSqlInstanceName DESKTOP-K6AKFTA\MSSQL2K19A
As stated in the Microsoft documentation, this function will display the value saved after the SQL Server installation. This value is will not be updated if the computer gets renamed.
By executing the above T-SQL script you’ll notice the return will not match the new computer name.
Fixing the SQL Server Configuration Server Name
To fix the SQL Server Configuration “@@SERVERNAME” function value you’ll need to use two of the Security Stored Procedures: “sp_dropserver” and “sp_addserver“. Below, I created a Dynamic SQL (T-SQL) Script solution to take care of this issue:
First, using the “sp_dropserver” to drop the server name:
-- -- Drop the wrong SQL Server Configuration @@Servername: -- Declare @TSql1 nvarchar(max); Declare @cfgSvrNameWithSqlInstName nvarchar(50); Set @cfgSvrNameWithSqlInstName = (Select @@SERVERNAME); Set @TSql1 = 'sp_dropserver ' + '''' + @cfgSvrNameWithSqlInstName + ''''; print @Tsql1; exec sp_executesql @TSql1; GO
Next, I use the “sp_addserver” to add back the correct server name:
--
-- Fix the SQL Server Configuration @@SERVENAME with SqlInstance Name:
--
Declare @TSql2 nvarchar(max);
Declare @fixSvrNameWithSqlInstName nvarchar(50);
Set @fixSvrNameWithSqlInstName = (Select convert(nvarchar, SERVERPROPERTY('servername')));
Set @TSql2 = 'sp_addserver ' + '''' + @fixSvrNameWithSqlInstName + ''''+ ', local';
print @TSql2;
exec sp_executesql @TSql2;
GO
Then, to verify the change works, use the following T-SQL statement:
Select @@SERVERNAME
Or, in SSMS, use the SQL Server Object Explorer, navigate to the “Security\Logins” folder, right-click in the User-Id, select “Properties“, and then go to “Securables“. Then, you’ll see the server name has been fixed.
Now, can this be automated? Yes, we can use PowerShell to automate this process.
That’s the next step.
Stay tuned for more!