May 9, 2013 at 8:27 am
Hello,
I have some code that creates a temporary table based on output from sp_who. I query the temp table to see if anyone is logged in as a certain generic userID from their own host. If they have, it's a misuse of the generic ID and a security breach. That part works fine.
Here's the part I need help with:
If the rowcount is not zero, I want to raise an alert and send an email. Could someone please help me with how I would check for this (code sample) or point me to a resource? I found a similar-sounding question but their intent was different enough that I still don't know how to do it.
I believe that I know how to set up a job and send an email. It's the code for checking for zero records I need an example for. Thanks.
May 9, 2013 at 9:33 am
here's a quick adaptation of another post i did for almost the same thing;
you'd need to tweak the mail settings, assuming you have db mail all set up:
--2005/08
IF (SELECT OBJECT_ID('Tempdb.dbo.#Results ')) IS NOT NULL
DROP TABLE #Results
CREATE TABLE #Results (
[ID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[SPID] CHAR (5) NULL,
[INSERTDT] DATETIME NULL DEFAULT(GETDATE()),
[STATUS] VARCHAR(30) NULL,
[LOGIN] VARCHAR(30) NULL,
[HOSTNAME] VARCHAR(30) NULL,
[BLKBY] VARCHAR(30) NULL,
[DBNAME] VARCHAR(30) NULL,
[COMMAND] VARCHAR(30) NULL,
[CPUTIME] INT NULL,
[DISKIO] INT NULL,
[LASTBATCH] VARCHAR(30) NULL,
[PROGRAMNAME] VARCHAR(200) NULL,
[SPIDINT] INT NULL,
[REQUESTID] INT NULL
)
--table exists, insert some data
INSERT INTO #Results(SPID,Status,Login,HostName,BlkBy,DBName,Command,CPUTime,DiskIO,LastBatch,ProgramName,SPIDINT,REQUESTID)
EXEC sp_who2
--don't care about spids less than 50 anyway:
DELETE FROM #Results WHERE SPIDINT < 50
IF EXISTS(SELECT 1 FROM #Results WHERE Login = 'DISNEY\lizaguirre')
BEGIN
DECLARE @Results varchar(max)
SET @Results = ''
SELECT @Results = @Results
+ 'EventDate: ' + CONVERT(VARCHAR(30),getdate(),121) + '<br />' + CHAR(13) + CHAR(10)
+ 'DBName: ' + DB_NAME() + '<br />'+ CHAR(13) + CHAR(10)
+ 'CurrentUser: ' + CURRENT_USER + '<br />'+ CHAR(13) + CHAR(10)
+ 'HostName: ' + HOST_NAME() + '<br />'+ CHAR(13) + CHAR(10)
+ 'ApplicationName: ' + APP_NAME() + '<br />'+ CHAR(13) + CHAR(10)
+ 'UserName: ' + USER_NAME() + '<br />'+ CHAR(13) + CHAR(10)
+ 'sUserName: ' + SUSER_SNAME() + '<br />'+ CHAR(13) + CHAR(10)
+ '[ORIGINAL_LOGIN]: ' + ORIGINAL_LOGIN() + '<br />'+ CHAR(13) + CHAR(10)
+ 'ipaddress: ' + client_net_address + '<br />'+ CHAR(13) + CHAR(10)
+ 'AuthenticationTyp: ' + auth_scheme + '<br />'+ CHAR(13) + CHAR(10)
+ REPLICATE ('-',15) + ' NEXT Session ' + REPLICATE ('-',15) + '<br />'+ CHAR(13) + CHAR(10)
FROM sys.dm_exec_connections
WHERE session_id IN (SELECT SPID FROM #Results WHERE Login = 'DISNEY\lizaguirre')
PRINT @Results
--we have some pretty html results, lets email them.
EXEC msdb.dbo.sp_send_dbmail
--@profile_name='Stormrage DBMail',
@recipients='lowell@someDomain.com',
@subject = 'Prohibited Logion Name Detected Durring Security Scan',
@body = @Results,
@body_format = 'HTML'
--@body_format = 'TEXT'
END
Lowell
May 9, 2013 at 11:44 am
Lowell (5/9/2013)
here's a quick adaptation of another post i did for almost the same thing;you'd need to tweak the mail settings, assuming you have db mail all set up:
--2005/08
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'#Results') AND xtype in (N'U'))
DROP TABLE #Results
CREATE TABLE #Results (
[ID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[SPID] CHAR (5) NULL,
[INSERTDT] DATETIME NULL DEFAULT(GETDATE()),
[STATUS] VARCHAR(30) NULL,
[LOGIN] VARCHAR(30) NULL,
[HOSTNAME] VARCHAR(30) NULL,
[BLKBY] VARCHAR(30) NULL,
[DBNAME] VARCHAR(30) NULL,
[COMMAND] VARCHAR(30) NULL,
[CPUTIME] INT NULL,
[DISKIO] INT NULL,
[LASTBATCH] VARCHAR(30) NULL,
[PROGRAMNAME] VARCHAR(200) NULL,
[SPIDINT] INT NULL,
[REQUESTID] INT NULL
)
--table exists, insert some data
INSERT INTO #Results(SPID,Status,Login,HostName,BlkBy,DBName,Command,CPUTime,DiskIO,LastBatch,ProgramName,SPIDINT,REQUESTID)
EXEC sp_who2
--don't care about spids less than 50 anyway:
DELETE FROM #Results WHERE SPIDINT < 50
IF EXISTS(SELECT 1 FROM #Results WHERE Login = 'DISNEY\lizaguirre')
BEGIN
DECLARE @Results varchar(max)
SET @Results = ''
SELECT @Results = @Results
+ 'EventDate: ' + CONVERT(VARCHAR(30),getdate(),121) + '<br />' + CHAR(13) + CHAR(10)
+ 'DBName: ' + DB_NAME() + '<br />'+ CHAR(13) + CHAR(10)
+ 'CurrentUser: ' + CURRENT_USER + '<br />'+ CHAR(13) + CHAR(10)
+ 'HostName: ' + HOST_NAME() + '<br />'+ CHAR(13) + CHAR(10)
+ 'ApplicationName: ' + APP_NAME() + '<br />'+ CHAR(13) + CHAR(10)
+ 'UserName: ' + USER_NAME() + '<br />'+ CHAR(13) + CHAR(10)
+ 'sUserName: ' + SUSER_SNAME() + '<br />'+ CHAR(13) + CHAR(10)
+ '[ORIGINAL_LOGIN]: ' + ORIGINAL_LOGIN() + '<br />'+ CHAR(13) + CHAR(10)
+ 'ipaddress: ' + client_net_address + '<br />'+ CHAR(13) + CHAR(10)
+ 'AuthenticationTyp: ' + auth_scheme + '<br />'+ CHAR(13) + CHAR(10)
+ REPLICATE ('-',15) + ' NEXT Session ' + REPLICATE ('-',15) + '<br />'+ CHAR(13) + CHAR(10)
FROM sys.dm_exec_connections
WHERE session_id IN (SELECT SPID FROM #Results WHERE Login = 'DISNEY\lizaguirre')
PRINT @Results
--we have some pretty html results, lets email them.
EXEC msdb.dbo.sp_send_dbmail
--@profile_name='Stormrage DBMail',
@recipients='lowell@someDomain.com',
@subject = 'Prohibited Logion Name Detected Durring Security Scan',
@body = @Results,
@body_format = 'HTML'
--@body_format = 'TEXT'
END
Thanks this is outstanding. I didn't write back right away because I have been trying to customize this a little so that I can screen for one particular SQL user ID, call it QES but only if the hostname is "not like" and then I have a list. I feel like a dunce because it's not working for me. The email works great but I have been fiddling with changing the where statement. I have :
IF EXISTS(SELECT 1 FROM #Results WHERE Login = 'QES' and hostname not like 'CLIENT%'
and hostname not in ('myhost','hosta', 'hostb', 'hostc','hostd'))
instead of :
WHERE session_id IN (SELECT SPID FROM #Results WHERE Login = 'DISNEY\lizaguirre')
I am getting many rows but none that are user QES. The currentuser and the username values are always dbo (schema), the SUserName is always my windowsID login to sql, none other.
Thanks. Do you know what I'm doing wrong? Your script works perfectly if I put my windows user id in there instead of Liz Aguirre. It's when I deviate from that as I described I don't get the results I expect.
Thanks again!
May 9, 2013 at 12:01 pm
well if you have a hardcoded list, you could modify it like this:
IF EXISTS(SELECT 1 FROM #Results
WHERE Login = 'QES'
AND ISNULL(HOSTNAME,'') NOT IN('','DEV214','DEV228','DBSQL2K5'))--no blank hostname?
it might be better to create an "allowed" table, in say master instead, and use that too:
IF EXISTS(SELECT 1 FROM #Results
WHERE Login = 'QES'
AND ISNULL(HOSTNAME,'') NOT IN(SELECT MachineName
From master.dbo.QESAllowedMachines) )
Notice i'm using the same test in two places, so make sure you tweak the script twice...once for the not exists, and once to get the whodunnit info
edit
also, my script doinked up thetest for a temp table; change it to something like this instead:
IF (SELECT OBJECT_ID('Tempdb.dbo.#Results ')) IS NOT NULL
DROP TABLE #Results
Lowell
May 9, 2013 at 1:25 pm
Thanks so very much Lowell. You really went out of your way to help. I appreciate it a lot. Works great.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply