September 15, 2008 at 11:54 am
I have a .Net process that I want to grant the following database specific permissions:
1. execute "ad-hoc" select queries
2. execute stored procedures
If the process only has SELECT access to table A, but executes a stored procedure which inserts data into table A, will the stored procedure fail?
September 15, 2008 at 12:03 pm
Yes. It will fail with EXECUTE permission denied on < Procedure name >
If you've granted the user the appropriate execute permissions then the insert will succeed. It's the concept of ownership chaining. As long as the procedure and the table have the same owner and a user has execute permissions on the procedure, permissions aren't checked for the table.
To do what you want, you need to grant that user SELECT on all the tables they will need (for the ad-hoc code) and EXECUTE permission on all the procs that it needs to run.
If you're using ad-hoc SQL from a front end, watch for SQL injection....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply