Permission denied error on object

Question

Permission denied error on object

Guras

SSCertifiable

Points: 6518
More actions
June 15, 2017 at 9:51 am

#326688
Executing the query "PROC_ACTION_INSERT ?,?" failed with the following error: "Cannot find the object 'PROC_ACTION_INSERT ', because it does not exist or you do not have permission.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly
I am getting the above error when running a SSIS shecduled as a job. When I run the SSIS from my box I do not get the error then again I am sysadmin on that database server.
The user1 that is running the job has all the needed permission and the stored procedure does exist in the database.
In the above stored procedure the following are done
1. Inserts , selects from a table --checked the permission and it the user1 has all the permission
2. Uses a function - func_all_states -- user1 has select permission on the function
3. executes dynamic sql
4. Creates and drops temp tables
Thanks
June 21, 2017 at 1:10 pm

This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

It's not the user that's running the job in whose context the commands run. If the user is a sysadmin, it'll run in the context of the SQL Server Agent account. Otherwise, it may use a proxy. Check the permissions on one of those accounts as appropriate.

John

Steve Jones - SSC Editor SSC Guru Points: 735657 More actions · Answer 2

Steve Jones - SSC Editor

SSC Guru

Points: 735657

June 15, 2017 at 10:22 am

#1946984

Create a SQL Server Agent Proxy

Guras SSCertifiable Points: 6518 More actions · Answer 3

Sorry this is not a sql job . It's a windows job running through task scheduler that calls a SSIS in the VB code. The job is run by user1 so the SSIS is also running in the user1 context .

Steve Jones - SSC Editor SSC Guru Points: 735657 More actions · Answer 4

Steve Jones - SSC Editor

SSC Guru

Points: 735657

June 15, 2017 at 11:11 am

#1947002

How does it call the SSIS package?

Guras SSCertifiable Points: 6518 More actions · Answer 5

Steve Jones - SSC Editor - Thursday, June 15, 2017 11:11 AM
How does it call the SSIS package?

Through the VB code.

Guras SSCertifiable Points: 6518 More actions · Answer 6

Guras - Thursday, June 15, 2017 12:44 PM
Steve Jones - SSC Editor - Thursday, June 15, 2017 11:11 AM
How does it call the SSIS package?
Through the VB code.

pkgResults = pkg.Execute();

Steve Jones - SSC Editor SSC Guru Points: 735657 More actions · Answer 7

You didn't list user1 with execute permission on the proc

Lowell SSC Guru Points: 323518 More actions · Answer 8

did you resolve this? when i read it, the first thing i though was it might be database context.
if the database context was master, you'd get that exact error, especially if the proc was in a differnet database.
i was expecting it to have db.schema.proc, like Production.dbo.PROC_ACTION_INSERT instead of just the proc name,and implied database and schema

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Guras SSCertifiable Points: 6518 More actions · Answer 9

Lowell - Wednesday, June 21, 2017 3:00 PM
did you resolve this? when i read it, the first thing i though was it might be database context.
if the database context was master, you'd get that exact error, especially if the proc was in a differnet database.
i was expecting it to have db.schema.proc, like Production.dbo.PROC_ACTION_INSERT instead of just the proc name,and implied database and schema

I checked the permissions on the database objects and all were fine. I tried again and it worked!! It works now but the thing that bothers me is I do not know the cause and fix for it 🙁

Lowell SSC Guru Points: 323518 More actions · Answer 10

I've seen this on a web server due to connection pooling, but it's been a while.
way back when , we resolved it via two ways: we assigned a default database for the account to NOT be master, but to be the actual database in question, and also made changes to use three part naming conventions for object names.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!