May 2, 2017 at 7:30 am
We have a developer here who created an after insert, update, delete trigger on a table. This trigger inserts, updates or deletes from a second table based on the action taken on the first, basically keeping the two tables in sync. The trigger then writes to a third table which simply logs what action(s) happened. Both of the tables the trigger is writing to were created at the same time, albeit in different schemas (of which there are many). All of this is based on a web client that is connecting to the database via a SQL Login that is granted db_datareader and db_datawriter permissions, along with execute on various and sundry stored procedures. Here's the strange part; The first table blows up without explicit permissions to insert, update or delete being granted to it, while the second one can be written to without any such permissions required.
Any idea why this might be?
May 2, 2017 at 7:38 am
It sounds as if there may be rights granted to the second table that you may have missed.
See if you can spot where the permissions were granted by running sp_helprolemember.
And, here's my rant.... Why use a trigger?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 2, 2017 at 7:56 am
Michael L John - Tuesday, May 2, 2017 7:38 AMIt sounds as if there may be rights granted to the second table that you may have missed.See if you can spot where the permissions were granted by running sp_helprolemember.
And, here's my rant.... Why use a trigger?
I'll do that after my meeting I'm headed to now, good idea. As to why use a trigger? I feel you, but some things are out of my hands. I hate hidden code personally, but alas -- I am just an admin in this world meaning expected to fix things after others screw them up lol....
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply