December 3, 2013 at 9:34 am
Hi All, How can I grant a Update Permission on a "Delete" trigger on a table? Thank you.
December 3, 2013 at 9:42 am
Grace09 (12/3/2013)
Hi All, How can I grant a Update Permission on a "Delete" trigger on a table? Thank you.
Permissions are not granted to a trigger. They are granted to a user.
If I understand your question correctly, you have a trigger on a table and in that trigger is an update statement. I suspect you are getting a permissions error because the current user does not have update permission to the table being updated?
One of the more common ways around this is to use EXECUTE AS. This will allow to have the trigger execute as user that has permission to perform the update.
http://technet.microsoft.com/en-us/library/ms188354.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 3, 2013 at 10:00 am
I have a trigger on a table, the trigger delete records from another table. I need to grant a permission to a user who can turn on/off the trigger. I hope that explains my question a little better. Thanks.
Sean Lange (12/3/2013)
Grace09 (12/3/2013)
Hi All, How can I grant a Update Permission on a "Delete" trigger on a table? Thank you.Permissions are not granted to a trigger. They are granted to a user.
If I understand your question correctly, you have a trigger on a table and in that trigger is an update statement. I suspect you are getting a permissions error because the current user does not have update permission to the table being updated?
One of the more common ways around this is to use EXECUTE AS. This will allow to have the trigger execute as user that has permission to perform the update.
December 3, 2013 at 11:55 am
Grace09 (12/3/2013)
I have a trigger on a table, the trigger delete records from another table. I need to grant a permission to a user who can turn on/off the trigger. I hope that explains my question a little better. Thanks.Sean Lange (12/3/2013)
Grace09 (12/3/2013)
Hi All, How can I grant a Update Permission on a "Delete" trigger on a table? Thank you.Permissions are not granted to a trigger. They are granted to a user.
If I understand your question correctly, you have a trigger on a table and in that trigger is an update statement. I suspect you are getting a permissions error because the current user does not have update permission to the table being updated?
One of the more common ways around this is to use EXECUTE AS. This will allow to have the trigger execute as user that has permission to perform the update.
http://technet.microsoft.com/en-us/library/ms189748.aspx
Scroll down to the permissions section. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 3, 2013 at 12:05 pm
Thank you, Sean. It works now. I just need to grant the Alter permission to the table.
December 3, 2013 at 12:07 pm
Excellent!!! Glad you got it working.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply