But I’m only running Select Queries!

Just because you only have db_datareader access to production doesn’t mean you can’t cause a great deal of harm.

I’ve worked in a few areas where against my advice, we have developers, analysts and report writers who  still have access to Production Environments. Normally most business owners have finally relented to my nagging/pressure of removing said access after a major incident but I’d rather not get to that stage. To assist me in my nagging, I set up a stored procedure that provides a report on people using production environments or primary nodes in an AG via SSMS for the last hour and I run this every hour (and a full 24 hour report at the end of each day) as an agent job so we can pick up long running queries as well as the reads and writes that query is producing. I can then use this to report back to the business when the inevitable email comes through saying ‘My system/application/database is slow’ or one of my own alerts are set off.

I’m using Adam Machanic’s sp_whoisactive to produce the report. My code just pulls out the data I require about SSMS usage.

You can set this up in either master or your own monitoring database (which is what I do). The Recipient defaults to the Operator email address (but this can be changed)  and whether you have any logins/users you want to ignore (e.g. myself or other DBAs). Please excuse the email format 🙂

USE [Tools]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('SSMS_PrimaryNode') IS NULL
  EXEC ('CREATE PROCEDURE SSMS_PrimaryNode AS RETURN 0;')
GO

ALTER PROCEDURE [dbo].[SSMS_PrimaryNode]
AS
BEGIN

DECLARE @ServerName NVARCHAR(50);
DECLARE @EMAIL_SUBJECT NVARCHAR(250);
DECLARE @SQL NVARCHAR(200);
DECLARE @EMAILADDRESS NVARCHAR(200);

SET @ServerName = @@SERVERNAME
SET @EMAIL_SUBJECT = N'Users running queries in Production Environment ' + @ServerName
SET @EMAILADDRESS = (SELECT Email_Address FROM msdb.dbo.sysoperators WHERE enabled = 1) 
IF OBJECT_ID('tempdb.dbo.##PNode','u') IS NOT NULL
BEGIN
DROP TABLE ##PNode
END

IF OBJECT_ID('tempdb.dbo.##WIA','u') IS NOT NULL
BEGIN
DROP TABLE ##WIA
END


CREATE TABLE ##WIA(
  [dd hh:mm:ss.mss] [varchar](8000) NULL,
  [session_id] [smallint] NOT NULL,
  [sql_text] [xml] NULL,
  [sql_command] [xml] NULL,
  [login_name] [nvarchar](128) NOT NULL,
  [wait_info] [nvarchar](4000) NULL,
  [tran_log_writes] [nvarchar](4000) NULL,
  [CPU] [varchar](30) NULL,
  [tempdb_allocations] [varchar](30) NULL,
  [tempdb_current] [varchar](30) NULL,
  [blocking_session_id] [smallint] NULL,
  [reads] [varchar](30) NULL,
  [writes] [varchar](30) NULL,
  [physical_reads] [varchar](30) NULL,
  [query_plan] [xml] NULL,
  [used_memory] [varchar](30) NULL,
  [status] [varchar](30) NOT NULL,
  [tran_start_time] [datetime] NULL,
  [open_tran_count] [varchar](30) NULL,
  [percent_complete] [varchar](30) NULL,
  [host_name] [nvarchar](128) NULL,
  [database_name] [nvarchar](128) NULL,
  [program_name] [nvarchar](128) NULL,
  [start_time] [datetime] NOT NULL,
  [login_time] [datetime] NULL,
  [request_id] [int] NULL,
  [collection_time] [datetime] NOT NULL
)

EXEC dbo.sp_WhoIsActive  @get_transaction_info = 1,  @get_outer_command = 1,  @get_plans = 1, @destination_table = ##WIA; 



SELECT 
distinct a.Session_id, a.login_name, a.CPU, a.blocking_session_id, a.reads, 
a.writes, a.used_memory, a.tran_start_time, a.host_name, a.program_name, a.start_time, b.[duration_dd_hh:mm:ss.mss]
INTO ##PNode
FROM ##WIA AS a
INNER JOIN (SELECT distinct session_id, login_name, MAX([dd hh:mm:ss.mss]) AS 'duration_dd_hh:mm:ss.mss'
FROM ##WIA
WHERE program_name LIKE '%Microsoft SQL Server Management Studio%'
GROUP BY Session_id, login_name) AS B
ON a.session_id = b.session_id
AND a.login_name = b.login_name
AND a.[dd hh:mm:ss.mss] = b.[duration_dd_hh:mm:ss.mss]
WHERE program_name LIKE '%Microsoft SQL Server Management Studio%'
AND start_time >= DATEADD(hh,-1,getdate())
--AND database_name = 'BBEC_V4' -- Database Specific
--AND a.login_name NOT IN ('<AddLoginHere') --add Logins here you want removed from the report. Or comment this line  out


IF (select count(*) from ##PNode) >0

BEGIN 

DECLARE @msg varchar(max), @tbl varchar(max), @tbl2 varchar(max), @tbl3 varchar(max)

SET @tbl = '<font face=verdana size=2><B>These people have used the Primary Node for read-only queries in the last hour </b></font><br /><br />
      <style type="text/css">h2, body {font-family: Arial, verdana;} table{font-size:11px; border-collapse:collapse;} td{background-color:#ffffff; border:1px solid black; padding:3px;} th{background-color:#46f610;border:1px solid black; padding:3px;}</style>
      <table cellpadding=2 cellspacing=2><tr><th><B>Login_Name</B></th><th><B>CPU</B></th><th><B>Reads</B></th><th><B>Writes</B></th><th><B>Start_time</B></th><th><B>Duration</B></th></tr>'
      + 
      cast((select td = Login_name , ''
        , td = CPU, ''
        , td = reads, ''
        , td = writes, ''
        , td = start_time, ''
  , td = [duration_dd_hh:mm:ss.mss], ''
        from ##PNode
         for xml path('tr'), type
        ) as varchar(max) )
      + '</table><BR>

      <font face=verdana size=2><B>Please Inform Staff that they should be using the Read-Only Node Only!</b></font><br /><br />'

exec msdb.dbo.sp_send_dbmail 
  @profile_name = @ServerName,
  @subject = @EMAIL_SUBJECT, 
  @body = @tbl, @body_format = 'HTML', 
  @recipients = @EmailAddress


END

DROP TABLE ##PNode
DROP TABLE ##WIA


END