November 28, 2003 at 7:18 am
Hello all. Certain users can't run stored proc and get this error even though they definitely have correct permissions and other users can run it ok (I can run sp manually from QA as well). Any ideas?
Paul
November 28, 2003 at 9:09 am
Can you tell us who is the owner of the sp, what the sp do, wich objects and owners that it query?
November 28, 2003 at 9:39 am
Here is the situation:
Windows 2000 user added to domain Global group. Global group added to Local group on SQL Server. Local group added to a user-created db role. This role has exec permissions on sp. sp owner is dbo and it is doing select against user table in DB (also owned by dbo).
November 28, 2003 at 10:37 am
It should work anyway.
Is there a chance that the users that can't exec the sp, has deny permissions on any other role, windows group on directly to their users?
December 1, 2003 at 2:10 am
No there are no deny permissions anywhwere. I have dropped and recreated the sp (with sysadmin rights) but this has had no effect.
December 1, 2003 at 6:44 am
It still sounds like the users that can't run it aren't members of the appropriate group. What happens if you grant the exec on the stored proc to 'public'? Can those users unable to execute it now then execute it?
Jeff
December 2, 2003 at 3:16 am
Problem solved. App. was calling another sp which incorrectly included a GRANT statement (no GO between sp and GRANT). This meant that every time user connected sp was trying to do the GRANT but couldn't because user doesn't have permission.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply