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 |
|
phenreid
Starting Member
29 Posts |
 Posted - 2011-11-08 : 00:07:03
|
| SS8/R2I have a sproc executed by asp.net (4.0). The user has been given execute permission on it.sproc updates table "A".Table "A" has a trigger associated with it. It's an audit trail trigger that uses dynamic SQL.I'm using SQL Server Authentication in ASP. The user has a "public role" overall. In the specific database, it has the sprocs in the securables all with execute permission.When executing the sproc from asp, I get an error that the trigger is not found or does not have adequate permission. Only granting the "control" permission on the underlying Table "A" solves this problem, but I do not really wish to give the user "control" permission. It sort of defeats the purpose of exposing the sprocs for execute-only. I've tried every combo of permission, but none of them work accept control on the table with the trigger.What's the best solution?Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-11-08 : 01:58:24
|
One way would be to impersonate the user against an user who has rights to the table and then execute the trigger using that users context.Put something like this in the triggerCreate trigger abcfor insert asexecute as user='the user who has rights to table' 'Your Dynamic sql'Exec(Dynamicsql)revert What will happen here would be that the user wont have explicit access to the table but the trigger will get executed using a different user context who has rights for the table.PBUH |
 |
|
|
|
|
|
Stored Procedure Trigger Permission Problem