CODEWITHSUNDEEP
sql-serversql-server-2008
Zath
Zath

Reputation: 557

EXECUTE AS USER failed for the requested user dbo in the database

Using sql server 2008.

First, I am not a DBA. But I can get around a db when it comes to sp's, triggers, tables, creating and normalizing. I'm a developer.

But with this new job, I inherited a large database that has many jobs that run.

My predecessor's account was disabled and of course a few jobs started to fail.

After searching around and testing and failing, I now need to post for some input on this problem.

The job fails when executing a stored procedure and I have checked it. It is updating the database in question.

First, I have changed the owner of the job:

 EXEC MSDB.dbo.sp_update_job 
 @job_name = 'MyJob', 
 @owner_login_name = 'NT AUTHORITY\SYSTEM' 
 GO

I have tried the sa account, sqlserveragent, and others but the same error occurs. And I made sure these new owners had the correct permissions for that database.

EXECUTE AS USER failed for the requested user dbo in the database MyDB.

The user never changes in the error message from the dbo user, even after I change the job owner.

Does anyone have any other suggestions that I may have overlooked?

Thanks,

UPDATE:

The problem has been solved. Learned something new today!

Take a look at this post: http://blog.strictly-software.com/2009/09/database-owner-sid-recorded-in-master.html

I hope this helps someone else in the future as I wasted some time on this problem and it took one small line of sql to fix it. Always seems to be that way, doesn't it?

Upvotes: 8

Views: 18226

Answers (3)

Vitaly Borisov
Vitaly Borisov

Reputation: 1193

For me user had permissions on DB level but no permissions on Server level were reflected. This helped:

USE AdventureWorks2012
EXEC sp_change_users_login 'Auto_Fix', 'Nathan'

Upvotes: 0

Nathan
Nathan

Reputation: 1705

Make the Database Trustworthy on your server:

ALTER DATABASE AdventureWorks2012 SET TRUSTWORTHY ON

Make the Job's owner be the same login that owns the Database:

USE AdventureWorks2012
GO
EXEC sp_changedbowner 'Nathan'
GO

Upvotes: 2

Zath
Zath

Reputation: 557

The problem has been solved. Learned something new today!

Take a look at this post: http://blog.strictly-software.com/2009/09/database-owner-sid-recorded-in-master.html

I hope this helps someone else in the future as I wasted some time on this problem and it took one small line of sql to fix it. Always seems to be that way, doesn't it?

Upvotes: 3

Related Questions