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 - 2002-09-11 : 14:09:02
|
| chad writes "I am attempting to run a dynamically created update statement as a stored procedure using EXEC or sp_executesql. The problem is that when you use either or these commands, the Stored Procudure is no longer run as the dbo (for exec, it changes to the user who called the sproc, for sp_executesql it changes to public). For security reasons, only the dbo has update permissions on the the table. Is there a way to get around this permissions problem or force the stored procedure to run as a certain user.ex.Table 'mytable' has update permissions for the user dboStored Procudere sprocDynamicUpdate has execute permissons for user WebUserTrying to run the following stored procedure as WebUser fails:CREATE PROCEDURE [sprocDynamicUpdate] ASDECLARE @statement NVARCHAR(500)SET @statement = 'UPDATE mytable SET field1 = ''helloword'''execute (@statement)GO" |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-09-11 : 14:40:16
|
| No.When using dynamic sql it uses the security context of the user running the procedure and all permissions are checked. For a great article on dynamic sql check out[url]http://www.algonet.se/~sommar/dynamic_sql.html[/url]HTHJasper Smith |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-09-11 : 15:03:19
|
| Dynamic SQL UPDATE statements just sounds like asking for trouble to me. Looks like you'll have to go with explicit procedures instead. |
|
|
|
|
|
|
Permissions change when using EXEC or sp_executesql to run dynamic sproc