Significant performance differences executing alter and grant statements between users with same permissions

All we need is an easy explanation of the problem, so here it is.

I am experiencing significant performances differences between users with the same permissions when executing alter and grant statements.

I have an automated build system that runs a series of SQL scripts for database changes – mostly alter table, alter procedure, with relevant grant statements.

While trying to change some things in my build process (including running under a different user account), I noticed that the current account running the scripts executes them up to 100x faster than any other user – even users with exactly the same permissions, and belonging to the same Active Directory group. I’ve tested SQL logins and Windows Authentication, with both results being basically identical.

The build process is run through a C# script in all cases. The script properly opens the connection only once, executes all the statements, then closes. This happens the same for all users. The script itself is called by a batch file, which in turn is called by a CI tool.

The average execution time for the current build user running this script is 12ms per statement, while the average execution time for any other user is 275ms per statement.

What possible causes could be contributing to this performance difference?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

TL;DR; Check database triggers

It turns our this was a very narrow, unique issue – but one that others might experience in a similar setup.

The performance issue was being caused by a database trigger that was called on grant, create, and alter statements, and based on the user, executed additional (sometimes heavy) logic.

These triggers were running extra logic for the accounts I was testing with, but not the original account.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply