March 6, 2002 at 10:09 am
We have a SQL7 (sp0) database (that for various reasons we're not allowed to patch or upgrade right now). It's running in mixed mode.
There is an Audit proc('uspAuditProc_Ins', or one like it depending on what's happening) that is exec'd in almost every SProc we have. The users are all web users, and data access is done through an MTS component running under a specified domain user. The domain user has EXECUTE permissions on both the parent procs and the child(audit) procs (actually they have execute on all procs and no access to the tables/views).
Now, the problem. When hitting these procs, I'll often (but not 100% of the time) get:
[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied on object 'uspAuditProc_Ins', database 'FIPS', owner 'dbo'.
Which is kind of odd because the user in question has EXECUTE permissions on that proc. If I rerun it (refresh in the browser) before that connection goes away (because it's pooled), it runs just fine. However, if I delay long enough that a new connection is created, it will give me the same error. My understanding of SQL Server security is that you can't exec a proc multiple times, and get permission denied the first time and allowed on any subsequent calls from the same connection...but it's happening.
Has anyone run into this before? Any insight would be most appreciated 🙂
Thanks
Chad
March 6, 2002 at 10:27 am
Could it be the underlying tables based on the data being inserted? Other than that, it seems strange? Have you run profiler to see if there is something you are missing?
Steve Jones
March 7, 2002 at 6:04 am
Normally if it is the underlying tables it barks the table name instead this specifically is referring to the SP itself and execute. Also try revoking the users right to this SP and then apply, then readd. Could be an internal error with the metadata tables which doing this might clear correct.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply