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 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)

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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. :-)

share|improve this answer
    
Hey, I edited in the question above- And yes, I have definitely learned to remember to configure the machine before installing SQL Server –  OrangeGrover Apr 5 '13 at 0:15
    
I think you'll also need to change the owner of the job under SQL Server Agent in Object Explorer. –  Aaron Bertrand Apr 5 '13 at 0:19
    
Yup that did the trick. It's strange though because it already VM-MSSQL-2008R2\sqladmin, but I re-entered it and saved and then it started working again. Thank you! –  OrangeGrover Apr 5 '13 at 0:25
    
@OrangeGrover interesting, I wonder what msdb..sysjobs.owner_sid said before you applied the change. It's possible the label was updated with the server name change but not the owner. –  Aaron Bertrand Apr 5 '13 at 0:42

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.