question

kpsenthilkumar86 avatar image
kpsenthilkumar86 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
sql-server-2012trigger
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

My question back to you would be, why did it work for four hours? What changed?
0 Likes 0 ·
Grant Fritchey Hi, Meanwhile, no changes made in that Logon trigger. whether the above T-sql code is correct.
0 Likes 0 ·
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
0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.