SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Login restriction from selected applications and users

By Subhash Chandra,

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.

  1. Make 1 MonitorDB or you can make it in master DB as well.
  2. 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)

)

  1. 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.
  2. Before enabling it please be careful and first enable the DAC (RemoteDacEnabled)setting from facts and surface area configuration or script.

Use master

GO

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

sp_configure 'remote admin connections', 1

GO

RECONFIGURE

GO

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

  1. 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

Total article views: 254 | Views in the last 30 days: 30
 
Related Articles
FORUM

Disable and enable trigger

Disable and enable trigger

FORUM

Error conecting because of Login Trigger

Error conecting because of Login Trigger

SCRIPT

Enable Disable All Triggers in a Database

Enable Disable All Triggers in a Database

FORUM

Need to find list of diable and enable triggers

Need to find list of diable and enable triggers

FORUM

Need to get list of disable and enable triggers from server

Need to get list of disable and enable triggers

Tags
auditing    
logins    
security    
 
Contribute