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
