I'm having a confusing problem after changing the computer name of a remote server hosting a local SQL Server instance.
Basically, a remote server was moved from one site to another. In order to facilitate this, I backed up and restored the old database to a new database name, clearing out the data so it could be used as a fresh database for the client software. I also changed the computer name, as we always do so to identify each server by its site number.
The database can be connected to by the client software just fine, and I can log in directly to SQL Server fine. However, one of my SQL Server Agent jobs fails, with an error in the event log:
SQL Server Scheduled Job 'Nightly Reset' (0x4F76FDFFF6DFFE4EA0DE4A70252AD3BD) – Status: Failed – Invoked on: 2012-02-07 08:10:05 – Message: The job failed. Unable to determine if the owner (Site-19\Admin) of job Nightly Reset has server access (reason: Could not obtain information about Windows NT group/user 'Site-19\Admin', error code 0x534. [SQLSTATE 42000] (Error 15404)).
Now, 'Site-19' is the old computer name, which has been changed, and the server has been reset. I connect manually using 'Site-28', the new site number, and it shows me as being connected to the SQL Server with Site-28\Admin. However, when I look at the properties of the Agent job, it shows the owner as being Site-19\Admin, and when I attempt to browse for users to change it, Site-28\Admin doesn't show up as an option, only Site-19\Admin. If I script out a new job from this one and manually change the owner to 'Site-28\Admin', the new job is created with the owner 'Site-19\Admin'.
Looking in sys.servers (or via sp_helpserver), I only have one entry: the current computer name. However, SELECT @@SERVERNAME returns the original development machine name (two name changes ago).
In short, I can't run this important SQL Server Agent job because it belongs to a user that no longer exists, and I can't figure out how to change it or create it as the correct user.
Best Answer
When you added the new server name using sp_addserver, did you remember to include the "local" designation. It is that tag that updates the metadata for @@SERVERNAME. More information.