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 got lucky with an Azure database restore…
  • The query was perfectly broken…
  • We are never coming undone with NOT IN…
  • Come learn with me, queries times will dive….
  • Transactions follow me left and right but who did that over here?

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.
  • Follow Following
    • Andrea Allred presents RoyalSQL
    • Join 221 other followers
    • Already have a WordPress.com account? Log in now.
    • Andrea Allred presents RoyalSQL
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar