[Event Notifications] and impersonation

Had this question pop up at #SQLSatAustin last month.  Finally had some time to futz with it.

Naomi Williams (@naomithesqldba) asked a security question I hadn't considered before.  "How does Event Notification deal with impersonation?"

Hmmm.  Good question.

Well, there are two different ways to do impersonation.  SETUSER and EXECUTE AS. 
Testing both, they appear to act the same. 

Our examples:

SELECT SUSER_SNAME()
go
SETUSER 'mydomain\mycoworkersname'
GO
SELECT SUSER_SNAME()
go
CREATE TABLE test_using_setuser (id INT IDENTITY)
GO
DROP TABLE test_using_setuser 

go
SETUSER
GO
SELECT SUSER_SNAME()
GO

EXECUTE AS LOGIN = 'mydomain\mycoworkersname'
CREATE TABLE test_using_executeaslogin (id INT IDENTITY)
REVERT

Let's look at the obvious event, the CREATE_TABLE.

 
 
Hmmm, looks like he did it, right?

Well, fortunately for him, I also capture trace events, via TRC_OBJECTS.  Which has more information that isn't normally needed.  But it's saved my bacon before, because it includes the App Name and Hostname.

And what does that one look like?




Busted!  Two different ways, in fact.  First of all, look at the HostName, which is my desktop.  Thought that can be worked around (most easily by connecting to the box itself - how long, after all, do your systems people store login info?).

However, the other is much more damning.  SessionLoginName, which clearly shows it was me.

Thanks, Naomi!