SQL SERVER – Prevent Users from Changing Index

Earlier this week, I wrote a blog post SQL SERVER – Trigger on Database to Prevent Table Creation. Many people like this blog post and asked me to follow up questions about whether this method can be used to prevent users from changing the index. Of course, you can use the same method. Let us see that in today’s blog post.

If you have ever worked with me on Comprehensive Database Performance Health Check , you probably know that I do not like to create too many indexes. The biggest challenge for many organisations is that many people have access to the database and often change the database schema, indexes as per their need.

I have seen often developers when are writing queries, create indexes that can speed up their query but it does not help the overall performance of the server. At one of my client’s places, we had over 100+ indexes created by various developers. This was a major issue. The best solution in this situation will be to create a user role that does not have permission to create an index. However, if you want an easy solution, you can also depend on the index. Here is how you can create a database trigger that will prevent any modification in the indexes.

Now when you try to create, modify or drop any index, it will give you the following error.

This database does not allow new Triggers.
Msg 3609, Level 16, State 2, Line 5
The transaction ended in the trigger. The batch has been aborted.

Well, as I mentioned earlier, I am not a big fan of triggers but I am not against database triggers. I would have created a new role with the necessary permissions for the database rather than using a trigger if it was me to prevent users.

If you have any questions, please feel free to reach out to me on Twitter.

Reference: Pinal Dave (http://blog.SQLAuthority.com)