Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Thanks for the link. On your suggestion, I asked it there as well. I do think it is valid here as well, as while the question is more infrastructure-related, the answer will very likely involve code, and there are plenty of SQL Server methodology questions here as well. –  Geo Ego Feb 7 '12 at 14:16
    
And what happens if you drop server 'Site-28'? What displays sp_helpserver? Can't you just delete old job and create new? –  Arvo Feb 7 '12 at 14:20
    
Interestingly enough, when I attempt to drop 'Site-28', it tells me that it can't be found. When I attempt to add it, it says that it already exists. If I create the job new, whether via the wizard or scripting it out from the original, it always creates it with 'Site-19\Admin' as the owner. –  Geo Ego Feb 7 '12 at 14:35
    
So the old, physical server was assigned a new name (and this change was also made in DNS) and SELECT @@SERVERNAME on the renamed box returns its new name? –  jl01 Feb 7 '12 at 14:46
1  
I merged the transferred question into this one, so all the answers are consolidated. –  jcolebrand Feb 8 '12 at 14:57

3 Answers 3

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
share|improve this answer
    
Yes, I did. I found the problem as described in the answer. Thanks for the help. –  Geo Ego Feb 22 '12 at 2:01

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'
share|improve this answer
    
Using that script, I was able to identify that I needed to manually drop the old server name and add the new one. I did so, but I still have the same problems. –  Geo Ego Feb 7 '12 at 15:11
    
Did you restart the instance? –  Mike Miller Feb 7 '12 at 15:16
    
Yes, the instance was restarted afterward. –  Geo Ego Feb 7 '12 at 15:47
    
Sorry dude gotta bow out, not sure what the problem might be. –  Mike Miller Feb 7 '12 at 15:57
    
That's alright. Thanks for the help! –  Geo Ego Feb 7 '12 at 18:02
up vote 1 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.