Blog Post

Restrict user to login from single Host

,

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

FOR LOGON

AS

BEGIN

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’

ROLLBACK;

END

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:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp

Rate

1 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

1 (1)

You rated this post out of 5. Change rating