Im trying to track down the usage of certain logins at the moment, we have a lot of SQL logins that haven’t been documented and legacy accounts people don’t know about. With that I needed a way to see if A: A login is used, B: from what application and C: who by. Of course Extended Events covers all these answers in a lovely way. below is the code to gather this information on either 2008 r2 or 2012+.

Below is my current code but as always now, you can download my latest version on github here

/*

XE - Monitor SA

	2012 + version
	2008 version

*/
-- 2012 +
CREATE EVENT SESSION [MonitorSA] ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    ACTION ( sqlserver.client_app_name, sqlserver.client_hostname,
    sqlserver.database_name, sqlserver.nt_username, sqlserver.sql_text,
    sqlserver.username )
    WHERE ( [sqlserver].[username] = N'sa' ) ) -- CHANGE TO LOGIN YOU WANT TO MONITOR
ADD TARGET package0.event_file ( SET filename = N'c:\temp\MonitorSA.xel',
    METADATAFILE = N'c:\temp\MonitorSA.xem' )
GO

ALTER EVENT SESSION MonitorSA ON SERVER
  STATE = START;
GO


/*

-- Read Results 

*/

SELECT  xdata.value('(event[@name="sql_statement_completed"]/@timestamp)[1]',
                    'datetime') [Execution_Time] ,
        xdata.value('(/event/action[@name="username"]/value)[1]',
                    'varchar(100)') AS [User] ,
        xdata.value('(/event/action[@name="client_hostname"]/value)[1]',
                    'varchar(100)') AS [ClientHost] ,
        xdata.value('(/event/action[@name="client_app_name"]/value)[1]',
                    'varchar(100)') AS [ClientApplication] ,
        xdata.value('(/event/action[@name="database_name"]/value)[1]',
                    'varchar(100)') AS [Database] ,
        xdata.value('(/event/action[@name="sql_text"]/value)[1]',
                    'varchar(max)') AS [SQLText]
FROM    ( SELECT    CAST(event_data AS XML)
          FROM      sys.fn_xe_file_target_read_file('C:\Temp\MonitorSA*.xel',
                                                    NULL, NULL, NULL)
        ) AS xmlr ( xdata )
ORDER BY xdata.value('(event[@name="sql_statement_completed"]/@timestamp)[1]',
                     'datetime') DESC;




/*

-- SQL Server 2008 CREATE



CREATE EVENT SESSION [MonitorSA] ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    ACTION ( sqlserver.client_app_name, sqlserver.client_hostname,
    sqlserver.database_id, sqlserver.nt_username, sqlserver.sql_text,
    sqlserver.username )
    WHERE ( [sqlserver].[username] = N'sa' ) ) -- CHANGE TO LOGIN YOU WANT TO MONITOR
  ADD TARGET package0.asynchronous_file_target( SET filename = N'c:\temp\MonitorSAr2.xel', 
      METADATAFILE = N'c:\temp\MonitorSA.xem' )
GO

ALTER EVENT SESSION MonitorSA ON SERVER
  STATE = START;
GO




-- SQL Server 2008 Query


SELECT  xdata.value('(event[@name="sql_statement_completed"]/@timestamp)[1]',
                    'datetime') [Execution_Time] ,
        xdata.value('(/event/action[@name="username"]/value)[1]',
                    'varchar(100)') AS [User] ,
        xdata.value('(/event/action[@name="client_hostname"]/value)[1]',
                    'varchar(100)') AS [ClientHost] ,
        xdata.value('(/event/action[@name="client_app_name"]/value)[1]',
                    'varchar(100)') AS [ClientApplication] ,
        DB_NAME(xdata.value('(/event/action[@name="database_id"]/value)[1]',
                    'int')) AS [Database] ,
        xdata.value('(/event/action[@name="sql_text"]/value)[1]',
                    'varchar(max)') AS [SQLText]
FROM    ( SELECT    CAST(event_data AS XML)
          FROM      sys.fn_xe_file_target_read_file('C:\Temp\MonitorSAr2*.xel','c:\temp\MonitorSA*.xem', NULL, NULL)
        ) AS xmlr ( xdata )
ORDER BY xdata.value('(event[@name="sql_statement_completed"]/@timestamp)[1]',
                     'datetime') DESC;

*/

Advertisements