webbmotorsports, 2018-04-11 (first published: 2018-03-29)

As in DBA life every organization have different -2 kind of user’s login tracking and login auditing requirement.

This trigger I had tested in SQL 2005 and later versions and its working fine till latest version

Requirements to enable this trigger.

Please make complete testing of this trigger on testing environment as per your need before implementing it on production. if you implement it on production without testing may have you face many kind of issues and challenges on daily life.

Make 1 MonitorDB or you can make it in master DB as well.
Create table as given below

create table tbl_login_hstry

(Login_name varchar(100),

Applicationname varchar(100),

HostName varchar(100),

SYSTEMUSER varchar(100),

Date datetime ,

DbName varchar(100)

)

Now create the trigger ---Default trigger will be in disabled as per your need and filtered programmes and after all requirement full fill you can enable it from management studio ----server objects and ----triggers –select trigger [Login_restriction_and_tracking_trigger] and right click on trigger name and make it enable.
Before enabling it please be careful and first enable the DAC (RemoteDacEnabled)setting from facts and surface area configuration or script.

Use master

/* 0 = Allow Local Connection, 1 = Allow Remote Connections*/

sp_configure 'remote admin connections', 1

RECONFIGURE

After implanting it now you can run select in tbl_login_hstry and see the user’s login history

If you face any issue you can drop this trigger by below commands.

C:\Users\Subhash>sqlcmd -S LocalHost -d master -A

1> DROP TRIGGER Login_restriction_and_tracking_trigger ON ALL SERVER

2> GO

USE [master] GO /****** Object: DdlTrigger [Login_restriction_and_tracking_trigger] Owner: Subhash chandra Script Date: 29-03-2018 16:02:22 ******/SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [Login_restriction_and_tracking_trigger] ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS /* create table tbl_login_hstry (Login_name varchar(100), Applicationname varchar(100), HostName varchar(100), SYSTEMUSER varchar(100), Date datetime , DbName varchar(100) ) truncate table tbl_login_hstry select * from tbl_login_hstry select * from tbl_login_hstry where hostname='abc' */BEGIN if APP_NAME () in ('Microsoft SQL Server Management Studio', 'Microsoft SQL Server Management Studio - Query', 'SQL Query Analyzer', 'OSQL-32','SQLCMD','Microsoft SQL Server Management Studio Express','Toad for SQL Server','Microsoft SQL Server Data Tools, T-SQL Editor')---add/remove your restricted programs begin if APP_NAME () in ('Microsoft SQL Server Management Studio', 'Microsoft SQL Server Management Studio - Query', 'SQL Query Analyzer', 'OSQL-32','SQLCMD','Microsoft SQL Server Management Studio Express','Toad for SQL Server','Microsoft SQL Server Data Tools, T-SQL Editor')--add/remove your restricted programs and ORIGINAL_LOGIN() in ('sa','Localhost_name\Administrator')---add/remove your restricted sql users which you don't want to allow there login through on above mention application program's. begin rollback end insert into MonitorDB..tbl_login_hstry select ORIGINAL_LOGIN(),APP_NAME (),HOST_NAME(),SYSTEM_USER,getdate(),db_name() end END GO ENABLE TRIGGER [Login_restriction_and_tracking_trigger] ON ALL SERVER GO