Sql-server – Changed server name, now maintenance plan fails

maintenance-planssql serversql-server-2008-r2

I am using SQL Server 2008 R2. When I try to execute a maintenance plan I get the following error:

Message [298] SQLServer Error: 15404, Could not obtain information
about Windows NT group/user 'WIN-6QFI9JAK804\sqladmin', error code
0x534. [SQLSTATE 42000] (ConnIsLoginSysAdmin)

I ran the following queries:

sp_dropserver 'WIN-OldName'
GO
sp_addserver 'VM-MSSQL-2008R2', local
GO

I then restarted the MSSQLSERVER service and the agent service.

When I run select @@servername I do get VM-MSSQL-2008R2 returned however the original error persists when I try to execute the maintenance plan again.

Is there another step that I haven't performed yet?

UPDATED:

I ran:

select * from msdb.dbo.sysssispackages

and noticed that the only plan is called Backups. So I instead ran the query:

UPDATE msdb.dbo.sysssispackages
  SET ownersid = SUSER_SID('VM-MSSQL-2008R2\sqladmin')
  WHERE name = 'Backups'

I ran the following two selects to make sure that the are the same:

select SUSER_SID('VM-MSSQL-2008R2\sqladmin')
select ownersid from msdb.dbo.sysssispackages WHERE name = 'Backups'

And they both return the same value.

When I run the plan, I get the same error message as before.

(I have created a new Backup Plan, but I'd still like to figure out why this one is working just for knowledge's sake)

Best Answer

You need to change the owner of the maintenance plan. It was set up to be owned by an account that no longer exists. Not sure if it is better in your scenario to use sa or the new local admin account. This update assumes you still have the sqladmin account after the rename and that it has been added as a login to SQL Server with admin privileges.

UPDATE msdb.dbo.sysssispackages
  SET ownersid = SUSER_SID('VM-MSSQL-2008R2\sqladmin')
  WHERE ownersid = SUSER_SID('WIN-6QFI9JAK804\sqladmin');

You may also need to reset the owner of the job associated with the maintenance plan, which you can do through the UI or via the following:

EXEC msdb..sp_update_job
  @job_name = N'MaintenancePlan.Subplan_1', -- or whatever it's called
  @owner_login_name = N'VM-MSSQL-2008R2\sqladmin';

If the login hasn't been added, then execute this first:

CREATE LOGIN [VM-MSSQL-2008R2\sqladmin] FROM WINDOWS;
GO
EXEC sp_addsrvrolemember N'VM-MSSQL-2008R2\sqladmin', N'sysadmin';

In the future, be sure to have the machine properly configured / named etc. before installing SQL Server. :-)

Related Question