Sql Server Agent job failing to execute stored procedure

Question

I have a stored procedure that I can execute in SSMS with a non domain SQL Server user.

This stored procedure selects data from tables in one database (DB1) truncates and selects into a table in DB2.

The user has datareader,datawriter and dbowner for both databases.

Problem:

When I execute the stored procedure via SS Agent with execute as the user I get the following error

The server principal [user] is not able to access the database [DB1] under the current security context.

Actions taken So far:

I have tried to resolve this so far by:

Turning on db chaining for both databases
Deleted the user from DB1 and added again
Checked using EXEC sp_change_users_login @Action=’Report’ to see if user orphaned. As this is a database that is a restore of a live one. However I added the user after the restore. The user was not listed as orphaned

@Giorgi Nakeuri added execute as caller to the stored proc and still not working via SSA — Peter Campbell, Jun 25 '15 at 15:28
You better ask the question in http://dba.stackexchange.com/ — Giorgi Nakeuri, Jun 25 '15 at 15:31
@PeterCampbell, one or the other db could not be trustworthy for the login context. ALTER DATABASE MyDatabase SET TRUSTWORTHY ON. — Emacs User, Jun 25 '15 at 15:33
@PeterCampbell, the only other thing that's obvious is if the user is properly mapped. User mappings should show both databases. — Emacs User, Jun 25 '15 at 15:39
@PeterCampbell, not to beat this to death, but are the users the same and not just two different users using the same login? — Emacs User, Jun 25 '15 at 15:54
@EmacsUser I have tried setting trustworthy on, but getting a permissions issue despite the user I am altering with being in the server sysadmin role as per https://msdn.microsoft.com/en-us/library/ms187861(v=sql.110).aspx — Peter Campbell, Jun 25 '15 at 15:54

score 1 · Accepted Answer · answered Jun 25 '15 at 15:44

1

A possible workaround if you don't want to have the owner be sa is to have the user be a member of msdb and grant the the SQLAgentOperatorRole in msdb. See if that works.

But to be honest, either use sa or a dedicated service account with enough permissions. It's better if the job runs under that context.

answered Jun 25 '15 at 15:44

Charles Farr

369
3
8

user already has SQLAgentOperatorRole in msdb, I agree sa would work, but its disabled on this instance. – Peter Campbell Jun 25 '15 at 15:50
set up a dedicated service account removed the local user from the SSA job and the job is running properly. – Peter Campbell Jun 25 '15 at 16:34
1

try this.. grant CONNECT and CONNECT REPLICATED to the user on both sides. – Charles Farr Jun 25 '15 at 16:36
1

Glad the dedicated service account worked for you. Its a cleaner way than the local user. Have fun, enjoy the day – Charles Farr Jun 25 '15 at 16:38

Sql Server Agent job failing to execute stored procedure

1 Answers1