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.

Improve this question
8
  • 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
    Commented Feb 7, 2012 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
    Commented Feb 7, 2012 at 14:20
  • 1
    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
    Commented Feb 7, 2012 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
    Commented Feb 7, 2012 at 14:46
  • 1
    I merged the transferred question into this one, so all the answers are consolidated.
    – jcolebrand ♦
    Commented Feb 8, 2012 at 14:57

4 Answers 4

Reset to default
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
Improve this answer
1
  • Just a note that @@ServerName didn't update until I restarted SQL Server
    – fiat
    Commented Feb 12, 2018 at 22:27
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.

Improve this answer
1
  • I applied this AND @brian-knight's answer. To change db ownerships, I used this SELECT 'use ' + DB_NAME(database_id) + ';EXEC sp_changedbowner ''sa'';' FROM sys.databases where DB_NAME(database_id) like 'MyDbs%';. After that I was able to drop the bad logon
    – fiat
    Commented Feb 12, 2018 at 22:29
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'
Improve this answer
4
  • 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
    Commented Feb 7, 2012 at 15:11
  • Did you restart the instance?
    – Mike Miller
    Commented Feb 7, 2012 at 15:16
  • Yes, the instance was restarted afterward.
    – Geo Ego
    Commented Feb 7, 2012 at 15:47
  • Sorry dude gotta bow out, not sure what the problem might be.
    – Mike Miller
    Commented Feb 7, 2012 at 15:57
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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