asked
SQL Server Logon Trigger problem
Hi All, We created the following Sql Server Logon Trigger on our Production server. Intention is to restrict the sql logins from accessing the databases through SSMS(restriction not applies for connection through web application). It worked fine for 4 hours, after that, it doesn't allowed any logins(including Windows Login). Due to this there was a Half-an-hour down in production. Is there any problem in the following code. Can anyone guide us in this regard. T-sql Code follows: CREATE TRIGGER Trigger_restrict_sql_logins ON ALL SERVER FOR LOGON AS BEGIN if (select type from sys.server_principals where name = ORIGINAL_LOGIN()) = 'S' and ORIGINAL_LOGIN() NOT IN ('sa') and app_name() = 'Microsoft SQL Server Management Studio - Query' begin ROLLBACK end END; Thanks in advance, K.P.Senthil Kumar
My question back to you would be, why did it work for four hours? What changed?
Grant Fritchey Hi, Meanwhile, no changes made in that Logon trigger. whether the above T-sql code is correct.
Check this code, it worked based on time.You can modify as per your requirement.
http://balkidba.blogspot.com/2013/09/logon-trigger.html?zx=676e603d8e179682
16 People are following this question.
