Logon Trigger

one of the great things i like about SQL 2005 is the implementation of the logon trigger. we have implemented at one of our clients which helps us track both sucessfull and unsucessfull logins. below is the script that i have used(given to me by Robert Djabrov). be sure that you have SQL 2005 SP2 along with the cumulative hotfix CU7 of SQL 2005 SP2.

/* ***************************************************************************
The script below creates all necessary objects to establish capability of capturing LOGON events against the instance where the script will be run. To check the last 100 LOGON events, pass 110 to the stored procedure,
e.g.: exec msdb.dbo.sp__logon_tracker 100
To display all events, do not pass any parameter, and it will return the last 2,100,000,000 records (2.1 billion). ****************************************************************************/
use msdb
go

if object_id('msdb.dbo.__logon_tracker') is not null
drop table msdb.dbo.__logon_tracker
go
use master
go
if exists (select * from sys.server_triggers where name = 'trg_logon_tracking')
drop trigger trg_logon_tracking on all server
go
create trigger trg_logon_tracking on all server with execute as 'sa' for logon
as
if object_id('msdb.dbo.__logon_tracker') is null
create table msdb.dbo.__logon_tracker ( logonEvent xml not null )
insert msdb.dbo.__logon_tracker select eventdata()
go
use msdb
go
if object_id('dbo.sp__logon_tracker') is not null drop procedure dbo.sp__logon_tracker
go
create procedure dbo.sp__logon_tracker
( @LastNumberOfEvents int = 2100000000)
as
select top (@LastNumberOfEvents) LogonTimeStamp = logonEvent.value('(/EVENT_INSTANCE/PostTime)[1]','datetime') ,LogonSPID = logonEvent.value('(/EVENT_INSTANCE/SPID)[1]','int') ,LoginName = logonEvent.value('(/EVENT_INSTANCE/LoginName)[1]','sysname') ,LoginClient = logonEvent.value('(/EVENT_INSTANCE/ClientHost)[1]','sysname')
from msdb.dbo.__logon_tracker order by 1 desc return (0)go

Comments

Post a Comment

Popular posts from this blog

Print from WPF using ReportViewer Control

Came across a requirements in a project that i was working on were we had to show SQL reports from inside of a WPF application(that WPF does not have a way to show SQL Reports requires another post). Could not find a complete solution that will give the user the code to add and show SQL Reports from WPF. The environment that i have used for this solution is Visual Studio 2010, SQL 2008  R2 deployed on a remote server Below is the complete code using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Windows; using System.Windows.Controls; using System.Windows.Data; using System.Windows.Documents; using System.Windows.Input; using System.Windows.Media; using System.Windows.Media.Imaging; using System.Windows.Navigation; using System.Windows.Shapes; namespace ReportViewer { /// /// Interaction logic for MainWindow.xaml /// public partial class MainWindow ...
Read more

Printing SSRS 2008 R2 Reports from C#.

While working on a code for sending a SSRS report straight to the printer, ran into a interesting issue where the Render method was not giving back the StreamId's for the reports which had more than one page. This is known bug in SSRS 2008 R3 MS Link . To  get around this issue, you have to modify the element value in the element and call the Render method multiple times  in a Do while loop to get all the pages for the report. The method RenderReport contains the code in which we iterate and call the Render method multiple times. Another bug, which i have not been able to resolve at the time of writing this post, is that the Fonts get somewhat strange when the report is published. Make sure that the you have the elements PrintDpiX and PrintDpiY present and thier values set to 96 in the element otherwise you might see the report printed out in oversize font. To call the class PrintSSSRSReport use the code below  PrintSSRSREport printReport = new PrintSSRSREport(); p...
Read more

Using IOptions class in .Net Core Unit Testing.

When implementing unit testng for a interface that was using a IOptions class, we were getting an null exception when trying to access the value using _mockMyconfig!.Object.Value . To resolve this error this, you have to manually create the MyConfig class and then assign it to the Value property. MyConfig myConfig = new MyConfig() { BaseURL = "https://mocktest.com" }; _mockMyconfig = new Mock >(MockBehavior.Loose); // We need to set the Value of IOptions to be the Myconfig Class, if this step is not done, then we get null exception when we try to use the value like this _mockMyconfig!.Object.Value _mockMyconfig.Setup(ap => ap.Value).Returns(myConfig); Once you have done this, then we use this in the unit testing like without getting the null exception. _mockHttpClient!.Setup(repo => repo.GetPWSHttpClient(_mockMyconfig!.Object.Value)).Returns(new HttpClient()).Verifiable(); The Main NuGet packages that have been used for th...
Read more