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
Date datetime ,
- 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.
/* 0 = Allow Local Connection, 1 = Allow Remote Connections*/
sp_configure 'remote admin connections', 1
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