tag:blogger.com,1999:blog-6101006543641561368.post1180935764182577990..comments2024-01-19T11:43:10.765-06:00Comments on sqlfingers.com: Using a SQL Server Logon Triggersqlfingershttp://www.blogger.com/profile/17197470820730005093noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-6101006543641561368.post-31113071292179359112017-01-12T09:24:25.889-06:002017-01-12T09:24:25.889-06:00concatenate all the information into an @Message v...concatenate all the information into an @Message variable and replace the Insert Into and following Select section with the xp_logevent call:<br /> exec xp_logevent 1000001, @Message, 'Informational' Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-6101006543641561368.post-40309431237970118492016-08-13T11:56:49.751-05:002016-08-13T11:56:49.751-05:00Adam, I'm curious to know how you'd change...Adam, I'm curious to know how you'd change the code so that this would show up in the logs. I'm still novice with TSQL code.Master Phttps://www.blogger.com/profile/08533628900256801317noreply@blogger.comtag:blogger.com,1999:blog-6101006543641561368.post-50979458923713994042016-08-02T16:23:09.022-05:002016-08-02T16:23:09.022-05:00Great script, but instead of logging it to a table...Great script, but instead of logging it to a table we put it into the SQL logs. This change meant I would not need to manage another db on my servers; by using "exec xp_logevent" we achieve the same as a table and it seems to work quite well for our means and tracks the users that need to be tracked.Anonymoushttps://www.blogger.com/profile/01425054398095367807noreply@blogger.comtag:blogger.com,1999:blog-6101006543641561368.post-88620218342501577952016-08-01T03:50:50.433-05:002016-08-01T03:50:50.433-05:00Thanks for your post, in my experience logon trigg...Thanks for your post, in my experience logon triggers can become really expensive to the point of creating serious performance issues depending on the number of logon events happening.<br /><br />A small improvement you can do in this trigger is first of all validate <br /><br />IF((ORIGINAL_LOGIN() = 'sa'))<br /><br />that way you can exit the trigger without further action in 99.9% of the cases. (unless you have everyone using 'sa', of course)<br /><br />Processing the xml and then asking would be a waste of resources.<br /><br />Raul Gonzalez @SQLDoubleGhttp://www.sqldoubleg.comnoreply@blogger.com