Impersonation is something that I really love in SQL Server 2014 and higher because it got so much better.  It is nice to be able to impersonate a user so I can see how the permissions will work and what errors that user will see without the pain of logging in as that user. Recently I found that the impersonation feature might be a security hole in my enterprise.  If you grant elevated permissions at any time to a user, your system could be at risk for someone to impersonate any login.   For example, I have an end user named “Joker” that at one point had elevated permissions.  We took them away, but weird things keep happening and we think it might be him.  We think he is impersonating other logins like this:

EXECUTE AS LOGIN = 'Heroes\Batman'

He then will run code that he shouldn’t be able to to run.

We can fix this with a simple SQL Script.  We could block Joker from impersonating Batman but then he might try Superman or Wonder Woman.  We want to stop him from ever doing this again.  This script will deny impersonation of any logins to a specific login. We would stop him like this:

DENY IMPERSONATE ANY LOGIN TO [Heroes\Joker]

Now our server is once again safe from any Joker that would try to harm our data.

The song for this post is Young and  Menace by Fall Out Boy