Renamed Administrator account, jobs now failing

paule123

I renamed the Administrator account to something else, and now my jobs are failing with this error in the event log for SQLSERVERAGENT:

SQL Server Scheduled Job 'MYSERVERNAME SQL Backup full daily backup job' (0x68ADBDA064F8B74AA8976E4A3B831346) - Status: Failed - Invoked on: 2009-10-10 22:42:43 - Message: The job failed. Unable to determine if the owner (MYSERVERNAME\Administrator) of job MYSERVERNAME SQL Backup full daily backup job has server access (reason: Could not obtain information about Windows NT group/user 'MYSERVERNAME\Administrator', error code 0x534. [SQLSTATE 42000] (Error 15404)).

Server is Windows 2008 Enterprise R2 x64, running SQL Server 2008 Enterprise SP1 x64.

petey

Have you updated the owner of the SQL Server Agent job that performs the backup task?

Thanks.

paule123

petey wrote:

Have you updated the owner of the SQL Server Agent job that performs the backup task?

Thanks.

The owner of the job is still MYSERVERNAME\Administrator and if I browse user objects, only the old MYSERVERNAME\Administrator user name is selectable -- the renamed admin user is not in the list.

Restarted SQL Server Agent hoping it might refresh the list, it did not.

Also noticed I am not being notified via email of job failure even though I specified this in the Red Gate GUI.

petey

Which account is being used to run the SQL Server Agent service?

As regards e-mail notification, SQL Backup only sends e-mail notifications on the backup/restore tasks that it performs. If the SQL Server Agent job itself fails to run, SQL Backup hasn't even had the chance to start, hence there is no e-mail notifications sent by SQL Backup. In these cases, you will need to use the SQL Server alert mechanisms.

paule123

SQL Server Agent is being run under a separate user account. I tried adding this user to the Administrators group, but the job still fails.

petey

If you were to create a new SQL Server Agent job, are you able to select the new account as the owner of the job?

Thanks.

paule123

Not exactly, strange behavior here... when creating a new job, the owner name is filled in with the correct renamed administrator user name. However if I go back in to view the job's properties, the job owner name has reverted back to the standard "Administrator" user name.

Edit: Suggestion from another poster on MS TechNet SQL Server forum was to make the job's owner "sa" ... I did this and now the job is successful.

paule123

Instead of using "sa" to run the job, here is another solution:

ALTER LOGIN [domain\old_administrator_name] WITH NAME = [domain\new_administrator_name]