Home » Posts tagged 'Server Level Trigger'
Tag Archives: Server Level Trigger
Restricting user to login from single host may be sometimes required from security point and other business requirements. We can achieve it through SERVER LEVEL LOGON TRIGGER.
ALTER TRIGGER TR_CHECK_LOGIN_TEST_HOST
ON ALL SERVER
DECLARE @HOSTNAME VARCHAR(48)
DECLARE @PROGNAME VARCHAR(100)
SELECT @HOSTNAME = HOST_NAME FROM SYS.DM_EXEC_SESSIONS
WHERE SESSION_ID = @@SPID
IF ORIGINAL_LOGIN() = ‘TEST’ AND @HOSTNAME =’HostName’
Once you create above trigger, Login “TEST” will be able to connect from specified host only. Connection from any other host will be failed and face below error: –
You can also find related errors in SQL Server Error log as well.
Error: 17892, Severity: 20, State: 1.
Logon failed for login ‘TEST’ due to trigger execution. [CLIENT: <local machine>]
Error: 3609, Severity: 16, State: 2.
The transaction ended in the trigger. The batch has been aborted.
Reference: Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN:-
Other Linked Profiles :-