CODEWITHSUNDEEP
sql-serverschemapermission-denied
IndieTech Solutions
IndieTech Solutions

Reputation: 2539

Permissions Issue when running a stored procedure

We are setting up a data analysis to refresh nightly and everything is working fine except for pulling the financial data from the SERVER2 The way we have it set up is to run a stored procedure on the SERVER1 which selects from the SummaryView from the SERVER2. We can run the stored procedure manually when we are logged in with the “admin” user. However, we cannot run it as a scheduled job. We are getting a schema lock permission on the SummaryView. any thoughts how to fix that?!

EDIT: Here's the error message

Executing the query "exec financial_sp" failed with the following error: "The OLE DB provider "SQLNCLI10" for linked server "Server02" does not contain the table ""XYZ"."dbo"."SummaryView"". The table either does not exist or the current user does not have permissions on that table.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Upvotes: 0

Views: 1725

Answers (1)

benjamin moskovits
benjamin moskovits

Reputation: 5468

The user that is being used to execute the scheduled job needs execute permission on the procedure.

Upvotes: 2

Related Questions