Andrea Allred presents RoyalSQL

Bringing happy endings to all your data stories.

Tag Archives: Impersonate

June 29, 2017 by andreaallred

Block the impersonating menace!

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

Posted in Awesome T-SQL, Security
Tagged All, Any, block, deny, Impersonate, Impersonation, Joker, Login, Logins, Menace, T-SQL, TSQL
Leave a comment

Post navigation

  • Pictures -Taken by Magic Mirrors
  • About the Royals
  • Playlist
  • SQL Server 2016 Discovery Day – SLC

News From The Kingdom

  • I’m Going Under and This Time DBCC Can’t Save Me…
  • If I need to rearrange my fragmentation, I will for you….
  • All of my friends say, “How much longer will it run?”
  • Block the impersonating menace!
  • SELECT * Is Bittersweet…I’m not trying to hurt you, I just love to query….

Enter your email address to follow this blog and receive notifications of new posts by email.

Find Your Data Story

Royals Magic

  • Register
  • Log in
  • Entries feed
  • Comments feed
  • WordPress.com
Blog at WordPress.com.