[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!

[Extended Events] Dynamically Shredding XML and PIVOTing the results for easy reading

I love Extended Events. I hate XML.  I love the dynamic shred for XML.  I hate having to write XML code when I can have it write itself.

And so....
 Here's an easy(er) way to deal with XE XML.
This XE is specifically to find queries with a duration longer than 1 second.


Results:


How, you ask?
We create an Extended Event for items of long-running duration.
We then feed it into a dynamic XML shredder ('local-name(.)'), that kind of thing.
We then take THOSE results and feed it into a Dynamic Pivot.

End result:
no crappy code that looks like:
event_data_XML.value('(event/data[3])[1]','INT') AS object_type,
event_data_XML.value('(event/data[4])[1]','INT') AS cpu,
event_data_XML.value('(event/data[5])[1]','INT') AS duration,

Enjoy!

[WAT] Fun length issue with REPLACE in SSMS "Results to"

Just had this burn me, and don't remember having seen this issue before.

REPLACE changes the length of your columns, in Results to Text/Results to File.

Run this piece of code in SSMS. Query->Results To->Grid.

SELECT TOP 100 REPLACE(REPLACE(name,'&',';'),'/','#'), name FROM sysdatabases

Copy and paste on successive lines.  The same.
Now do the same thing, in Results to Text...
And now the same, Results to File...

Note that they're no longer the same length.  Seems the case at least since 2005.
The length of the header, which dictates the length (if doing fixed-width stuff) is twice as long.  That occurs even when the replaced characters are the same length.

Now let's see what our 2012++ friend, which gives us the metadata for a query, says.

SELECT * FROM sys.dm_exec_describe_first_result_set('
SELECT TOP 100 replace(replace(name,''&'','';''),''/'',''$'') as rname, name
FROM sysdatabases', NULL, 0)

Which gets you this fun nugget...



Hey look!  It went from nvarchar(128) (aka sysname) to nvarchar(4000).  Interesting!

Maybe this is normal/expected behavior.  But it made life difficult today, and experience is gained from doing it wrong, so here's your experience for the day.