Login restriction from selected applications and users

,

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

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

Rate

3.67 (3)

Share

Share

Rate

3.67 (3)