10

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.

Geo Ego
  • 271
  • 1
  • 2
  • 8

4 Answers4

7

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.

sp_addserver 'servername', local
Brian Knight
  • 359
  • 1
  • 3
7

I found the answer yesterday with the help of a friend of mine. I had to log in via SSMS with a user other than the Windows login I was attempting to use, delete the old login, and add my Windows login again. After that, I was able to transfer ownership of the job properly, SQL was able to get the user data from Windows, and all was right with the world.

Geo Ego
  • 271
  • 1
  • 2
  • 8
4

I use the following to identify problems and build the correct drop and add statements, if you get ALL OK, then you don't need to do anything otherwise you need to run the commands.

declare @currentName as nvarchar(128)
declare @newName as varchar(max)
declare @serverName as varchar(max)
declare @serverInstance as varchar(max)

select  @currentName = @@SERVERNAME
select @serverInstance = cast(serverproperty('InstanceName') as varchar(max))
select  @serverName = cast(serverproperty('MachineName') as varchar(max))

set @newName = @serverName

if (@serverInstance <> '') 
begin
      set @newName = @serverName + '\' + @serverInstance
end

if (@currentName <> @newName)
Begin
      print 'sp_dropserver ''' + @currentName + '''';
      print 'go'
      print 'sp_addserver ''' + @newName + ''',local'
      print 'go'
end
else
Print 'ALL OK'
Mike Miller
  • 141
  • 1
0

Had a similar issue: changed the hostname of a computer where SQL Server and SQL Server Agent is running. Jobs were assigned to . After creating a temp user / logon to SSMS using this new temp user / drop and create the login name (public and sysadmin privs!) / re-assign the jobs to this re-created Login everything was fine. Maybe you could manipulate a system table to reflect the same change; but above method is not that risky.

Martin Bruegger
  • 1