Earlier I have two blog posts on the database trigger and I have received lots of questions and comments on them. Here are the blog posts: SQL SERVER – Prevent Users from Changing Index and SQL SERVER – Trigger on Database to Prevent Table Creation. Many people asked me to create a video on the same topic, so we have Prevent Unauthorized Index Modifications.
Here is the video from SQL in Sixty Seconds series.
If you are going to say that I should have created a user role or application role (to prevent unauthorized index modification) with the right amount of permissions, I totally agree with you. However, this solution was chosen by my client and they find it very easy to implement. If you have ever worked with me before on Comprehensive Database Performance Health Check, you would know that I do not like triggers.
Here are my few recent videos and I would like to know what is your feedback about them.
- MAX Columns Ever Existed in Table – SQL in Sixty Seconds #182
- Tuning Query Cost 100% – SQL in Sixty Seconds #181
- Queries Using Specific Index – SQL in Sixty Seconds #180
- Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
- One Scan for 3 Count Sum – SQL in Sixty Seconds #178
- SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
- COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176
Reference:Â Pinal Dave (http://blog.SQLAuthority.com)
2 Comments. Leave new
I hope, this will restrict all user to create index, so how can we create an index if really required. do we need to delete this trigger?
Yes, you will need to disable or delete the trigger.
As I mentioned, It would be best to create a security role.