Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-09-20 : 07:15:34
|
| factorX writes "Hi all,I have encountered a strange situation in assigning permissions for a user.The scenario was the following:I needed to build a new login for an application accessing a sql server 2K5. The app was accessing just 3 stored procedures.I created a new sql server login, database user and granted execute on those 3 stored procedures for the new user.When run the application failed because the new created user had no permissions to insert information in a table.The INSERT statement was contained into one of the stored procedures, but was part of a dynamic built query stored into a varchar variable and executed via an EXEC @query statement.Why is this different from a regular INSERT in procedure code which is not causing any permission violation?Thank you" |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-09-20 : 07:46:47
|
| Here is what BOL says on EXECUTE statement permissions:"EXECUTE permissions for a stored procedure default to the owner of the stored procedure, who can transfer them to other users. Permissions to use the statement(s) within the EXECUTE string are checked at the time EXECUTE is encountered, even if the EXECUTE statement is included within a stored procedure. When a stored procedure is run that executes a string, permissions are checked in the context of the user who executes the procedure, not in the context of the user who created the procedure. However, if a user owns two stored procedures in which the first procedure calls the second, then EXECUTE permission checking is not performed for the second stored procedure."Harsh AthalyeIndia."Nothing is Impossible" |
 |
|
|
|
|
|
permissions issue