December 3, 2015 at 3:02 pm
Hello,
I need to create some trigger or some other mechanism so that I can get email when developers login to SQL Server Management studio using application user account.
December 3, 2015 at 3:24 pm
I tried this code but seems like something is wrong and it generated more than 2500 emails just for 1 event that I tested.
USE [msdb]
GO
-- Create a queue
CREATE QUEUE Q_Logon_Triggers_Queue;
GO
--------------------------------------------------------
USE [msdb]
GO
-- Create a service
CREATE SERVICE S_Logon_Triggers_Service
ON QUEUE Q_Logon_Triggers_Queue([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO
----------------------------------------------------------
USE [msdb]
GO
-- Create a route
CREATE ROUTE R_Logon_Triggers_Route
WITH SERVICE_NAME = N'S_Logon_Triggers_Service'
, ADDRESS = N'LOCAL';
GO
--------------------------------------------------------------
USE [msdb]
GO
/* current user get ownership of EVENT NOTIFICATION, so switch to 'sa' */
EXEC AS LOGIN = 'sa';
go
-- Create the event notification at the server level for the AUDIT_LOGIN event
CREATE EVENT NOTIFICATION N_Login_Notification
ON SERVER FOR AUDIT_LOGIN
TO SERVICE 'S_Logon_Triggers_Service', 'current database';
go
/* Switch back to original user */
REVERT;
GO
----------------------------------------------------------------------------------------------
USE [msdb]
GO
ALTER PROCEDURE [dbo].[p_dba_appuser_login_ssms_events]
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON;
WHILE (1 = 1)
BEGIN
DECLARE @messageBody VARBINARY(MAX);
DECLARE @messageTypeName NVARCHAR(256);
WAITFOR (
RECEIVE TOP(1)
@messageTypeName = message_type_name,
@messageBody = message_body
FROM Q_Logon_Triggers_Queue
), TIMEOUT 500
-- If there is no message, exit
IF @@ROWCOUNT = 0
BEGIN
BREAK ;
END ;
-- If the message type is EventNotification do the actual work
IF (@messageTypeName = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification')
BEGIN
DECLARE @XML XML,
@host_name varchar(128) ,
@program_name varchar(128) ,
@nt_domain varchar(128) ,
@nt_user_name varchar(128) ,
@login_name varchar(128) ,
@original_login_name varchar(128) ,
--@client_net_address varchar(48) ,
@Database_Name varchar(128) ,
@ts_logon datetime,
@Subject_line Varchar(200),
@body_line Varchar(4000),
@SPID VARCHAR(5);
SELECT @XML=CONVERT(XML,@messageBody)
,@host_name = ''
,@program_name = ''
,@nt_domain = ''
,@nt_user_name = ''
,@login_name = ''
,@original_login_name = ''
--,@client_net_address =''
,@SPID ='';
-- Get the SPID and the Login name using the value method
SELECT @SPID = @XML.value('(/EVENT_INSTANCE/SPID)[1]', 'VARCHAR(5)')
, @ts_logon = @XML.value('(/EVENT_INSTANCE/StartTime)[1]', 'NVARCHAR(128)')
, @host_name = @XML.value('(/EVENT_INSTANCE/HostName)[1]', 'NVARCHAR(128)')
, @program_name = @XML.value('(/EVENT_INSTANCE/ApplicationName)[1]', 'NVARCHAR(128)')
, @nt_domain = @XML.value('(/EVENT_INSTANCE/NTDomainName)[1]', 'NVARCHAR(128)')
, @nt_user_name = @XML.value('(/EVENT_INSTANCE/NTUserName)[1]', 'NVARCHAR(128)')
, @original_login_name = @XML.value('(/EVENT_INSTANCE/SessionLoginName)[1]', 'NVARCHAR(128)')
, @login_name = @XML.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(128)')
, @Database_Name = @XML.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(128)')
;
SET @Subject_line = 'Application user tried to login to ssms on '+@host_name+' Detected'
SET @body_line = 'Application user ' + @login_name+ ' tried to login to SSMS from ' + @host_name+ ' from '+ @program_name --+'.' -- ' at ' + @login_time +'.'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'dev',
@recipients = 'abc@abc.com',
@subject = @subject_line,
@body = @body_line;
END
END
END
--END
GO
ALTER QUEUE Q_Logon_Triggers_Queue
WITH ACTIVATION
(
STATUS = ON,
PROCEDURE_NAME = [dbo].[p_dba_appuser_login_ssms_events],
MAX_QUEUE_READERS = 1,
EXECUTE AS OWNER
);
GO
December 3, 2015 at 4:55 pm
Why not just use a logon trigger? You could even prevent that logon from occurring.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 3, 2015 at 4:59 pm
Looking at this I wonder why not just use dbmail instead of the event notification?
In addition, a logon trigger would suit nicely here.
I am also missing how this results in false.
WHILE (1 = 1)
Looks to me like an infinite loop if the rowcount is greater than 0. But maybe I missed something in the code.
In short, this is overkill for what is needed. The code at http://www.sqlservercentral.com/Forums/FindPost1742443.aspx would work just find. Just throw a sp_send_dbmail in lieu of the raiserror.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 3, 2015 at 10:53 pm
Thnaks but in the same thread if you go to page-3, U will see my code which says, and i still have issues.
--SELECT * FROM sys.dm_exec_sessions order by login_time desc
USE [master]
GO
/****** Object: DdlTrigger [TR_LOGON_APP] Script Date: 12/3/2015 11:56:37 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [TR_LOGON_APPUSER_SSMS]
ON ALL SERVER
FOR LOGON
AS
BEGIN
DECLARE @program_name nvarchar(128)
DECLARE @host_name nvarchar(128)
DECLARE @login_name nvarchar(128)
DECLARE @login_time DATETIME
DECLARE @Subject_line nvarchar(128)
DECLARE @body_line nvarchar(MAX)
SELECT @program_name = program_name,
@host_name = host_name,
@login_name = login_name,
@login_time = login_time
FROM sys.dm_exec_sessions AS c
WHERE c.session_id = @@spid
IF ORIGINAL_LOGIN() LIKE '%user' OR ORIGINAL_LOGIN() LIKE 'ssis%' OR ORIGINAL_LOGIN() LIKE 'bo%'
AND @program_name LIKE '%Management%Studio%'
BEGIN
--RAISERROR('This login is for application use only.',16,1)
SET @Subject_line = 'Application user tried to login to ssms on '+@host_name+' Detected'
SET @body_line = 'Application user ' + @login_name+ ' tried to login to SSMS from ' + @host_name+ ' from '+ @program_name+ ' at ' + @login_time +'.'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'dev',
@recipients = 'abc@abc.com',
@subject = @Subject_line,
@body = @body_line
--ROLLBACK;
END
END;
GO
December 4, 2015 at 1:48 am
I had to do something quite similar this year and I can share my experience with it.
Personally, I would avoid both logon triggers and service broker for this particular problem.
Logon triggers can slow down the application significantlty, even when the trigger code does nothing at all.
Service broker can sometimes go nuts and start throwing poison messages. You don't want that to happen for an event that fires hundreds of times per second: it will quickly fill your sysxmitqueue table in msdb (true story).
What I ended up doing is this:
1) create an extended events session for the logon events
2) watch the session with the XE streaming API
3) send an email (or whatever makes sense for you) from the app that reads the stream
I have an example of streaming XEvents from powershell here: http://spaghettidba.com/2015/04/20/tracking-table-usage-and-identifying-unused-objects/
-- Gianluca Sartori
December 4, 2015 at 2:11 am
Here is a quick and dirty example.
First, you need an event session:
CREATE EVENT SESSION [Audit_Logon] ON SERVER
ADD EVENT sqlserver.LOGIN (
SET collect_database_name = (1)
,collect_options_text = (0)
ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.server_principal_name)
)
WITH (
MAX_MEMORY = 4096 KB
,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
,MAX_DISPATCH_LATENCY = 30 SECONDS
,MAX_EVENT_SIZE = 0 KB
,MEMORY_PARTITION_MODE = NONE
,TRACK_CAUSALITY = OFF
,STARTUP_STATE = ON
)
GO
Then you need a script to capture the events and process them.
[CmdletBinding()]
Param(
[Parameter(Mandatory=$True,Position=1)]
[string]$servername
)
sl $Env:Temp
Add-Type -Path 'C:\Program Files\Microsoft SQL Server\120\Shared\Microsoft.SqlServer.XE.Core.dll'
$connectionString = 'Data Source=' + $servername + '; Initial Catalog = master; Integrated Security = SSPI'
$SessionName = "Audit_Logon"
# connect to the Extended Events session
[Microsoft.SqlServer.XEvent.Linq.QueryableXEventData] $events = New-Object -TypeName Microsoft.SqlServer.XEvent.Linq.QueryableXEventData `
-ArgumentList @($connectionString, $SessionName, [Microsoft.SqlServer.XEvent.Linq.EventStreamSourceOptions]::EventStream, [Microsoft.SqlServer.XEvent.Linq.EventStreamCacheOptions]::DoNotCache)
$events | % {
$currentEvent = $_
$database_name = $currentEvent.Fields["database_name"].Value
if($client_app_name -eq $null) { $client_app_name = [string]::Empty }
$original_login_name = $currentEvent.Actions["server_principal_name"].Value
$client_app_name = $currentEvent.Actions["client_app_name"].Value
$client_host_name = $currentEvent.Actions["client_hostname"].Value
#send email
if((($original_login_name -like "*user") -or ($original_login_name -like "ssis*") -or ($original_login_name -like "bo*")) -and $client_app_name -like "*Management*Studio*")
{
$subject = "Application user tried to login to ssms on " + $client_host_name + " detected "
$body = "Application user " + $original_login_name + " tried to login to SSMS from " + $client_host_name + " from " + $client_app_name + " at " + Get-Date
Send-MailMessage -To "dba@mycompany.com" -Subject $subject -Body $body
}
}
The easiest way to run the script is from a SQL Agent job:
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBA_COLLECT_AUDIT_LOGIN',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [COLLECT] Script Date: 04/12/15 10:06:56 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'COLLECT',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'CmdExec',
@command=N'powershell -File X:\scripts\collect_audit_login.ps1 -servername $(ESCAPE_DQUOTE(SRVR))',
@flags=0,
@proxy_name=N'SomeProxyAccountThatCanSendEmail'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'EVERY MINUTE',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=1,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20150227,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'0db89756-e08f-4e7f-914a-4137518608fe'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
The job will alway be running, but I'm ok with that.
A good improvement could be filtering the events directly in the session definition instead of filtering them in powershell. I'll leave that excercise for you.
BTW, in the previous thread you mentioned that the target instance was SQL 2014, but you posted in the SQL 2008 forum. Obviously, this code won't work in 2008.
Hope this helps.
Gianluca
-- Gianluca Sartori
December 4, 2015 at 7:20 am
Thanks but unfortunately we r still on sql 2005 server and this feature is not there.
December 4, 2015 at 7:33 am
Let's see if I understand correctly:
* you said you're on 2014
* you posted in the 2008 forums
* you are on 2005 instead
OK, makes sense now 🙂
-- Gianluca Sartori
December 4, 2015 at 9:16 am
Where exactly I said its 2014? I don't remember but if I said I m so sorry.
And I put it in 2008 becuase 2005 and 2008 are still very similar and no one reads 2005 forum so to get faster answer I put it in 2008.
December 4, 2015 at 9:31 am
dallas13 (12/4/2015)
Where exactly I said its 2014? I don't remember but if I said I m so sorry.And I put it in 2008 becuase 2005 and 2008 are still very similar and no one reads 2005 forum so to get faster answer I put it in 2008.
No worries, I'm just joking 🙂
BTW, I think it was here: http://www.sqlservercentral.com/Forums/FindPost1702825.aspx
However, I'm sorry that the solution I offered was not useful for you.
In 2005 I would use logon triggers. Service broker is too dangerous in my opinion, especially if you're not monitoring the queues attentively.
-- Gianluca Sartori
December 4, 2015 at 11:06 am
dallas13 (12/3/2015)
Thnaks but in the same thread if you go to page-3, U will see my code which says, and i still have issues.
--SELECT * FROM sys.dm_exec_sessions order by login_time descUSE [master]
GO
/****** Object: DdlTrigger [TR_LOGON_APP] Script Date: 12/3/2015 11:56:37 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [TR_LOGON_APPUSER_SSMS]
ON ALL SERVER
FOR LOGON
AS
BEGIN
DECLARE @program_name nvarchar(128)
DECLARE @host_name nvarchar(128)
DECLARE @login_name nvarchar(128)
DECLARE @login_time DATETIME
DECLARE @Subject_line nvarchar(128)
DECLARE @body_line nvarchar(MAX)
SELECT @program_name = program_name,
@host_name = host_name,
@login_name = login_name,
@login_time = login_time
FROM sys.dm_exec_sessions AS c
WHERE c.session_id = @@spid
IF ORIGINAL_LOGIN() LIKE '%user' OR ORIGINAL_LOGIN() LIKE 'ssis%' OR ORIGINAL_LOGIN() LIKE 'bo%'
AND @program_name LIKE '%Management%Studio%'
BEGIN
--RAISERROR('This login is for application use only.',16,1)
SET @Subject_line = 'Application user tried to login to ssms on '+@host_name+' Detected'
SET @body_line = 'Application user ' + @login_name+ ' tried to login to SSMS from ' + @host_name+ ' from '+ @program_name+ ' at ' + @login_time +'.'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'dev',
@recipients = 'abc@abc.com',
@subject = @Subject_line,
@body = @body_line
--ROLLBACK;
END
END;
GO
What is the error that you are getting? Or what is the issue with this code that you are experiencing
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 4, 2015 at 11:55 am
When I use this,
IF ORIGINAL_LOGIN() LIKE '%user' OR ORIGINAL_LOGIN() LIKE 'ssis%' OR ORIGINAL_LOGIN() LIKE 'bo%'
After then when application user logs in, it throws error which I don't want.
So instead I used below code but then user logs in but I dont get emails. Also no errors in error log as well as sql db mail logs. which is starnge.
IF @login_name LIKE '%user' OR @login_name LIKE 'ssis%' OR @login_name LIKE 'bo%'
December 7, 2015 at 7:58 am
My code is working but its sending thousands of emails for 1 event. Isn't that abnormal?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply