February 13, 2016 at 12:34 pm
I have a problem with SQL Server 2008 R2 not being able to execute a sql server job. This involves two linked SQL Server database servers. The SQL Server job exists on ServerA but is performed on ServerB. When I attempted to run the job, I receive the following error:
Executed as user: Domain\TestUser. The EXECUTE permission was denied on the object 'olr_transfer_master', database 'Test_Online_DB', schema 'dbo'. [SQLSTATE 42000] (Error 229). The step failed.
The login Domain\TestUser is a db_owner of all user databases on both ServerA and ServerB (including Test_Online_DB). Also, the login Domain\TestUser has been granted execute permissions on the database Test_Online_DB on ServerB. The errors began when I restored the database from a backup. I don't understand why the login Domain\TestUser has db_owner permissions and execute permissions but the error still happens. Any assistance would be appreciated.
February 13, 2016 at 2:46 pm
Could the user be orphaned? I might double check.
Make another job step, same user, capture suser_sname() and user_name(), try to understand where permissions might be broken.
February 13, 2016 at 6:04 pm
I checked for orphaned users and when I ran a query, I did not find any orphaned users.
February 16, 2016 at 11:35 am
Did you get the context of the job?
February 16, 2016 at 1:06 pm
The issue has been resolved. The problem was with the linked server. The login used by the linked server to connect the two sql server database servers needed elevated permissions. Once that was done, the problem was resolved.
February 16, 2016 at 10:19 pm
I found the similar question here: http://www.sqlservercentral.com/Forums/Topic463688-146-1.aspx
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply