|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Monday, September 07, 2009 3:56 AM
Points: 116,
Visits: 46
|
|
|
|
|
|
Hall of Fame
       
Group: General Forum Members
Last Login: Thursday, November 05, 2009 12:49 PM
Points: 3,425,
Visits: 493
|
|
This is a good article! Question: the stored procedure LogginProc is doing the endless loop waiting for the logon event description to be queued and when it finds one it pulls the information from the queue. Is it really a trigger? I can do the similar thing with the traces. You just set up a server-side trace with logging to the trace file. Than the logon events will be logged to the trace file. Then you can select from the trace file into any table or you may select to keep this info in the trace file because you can query it too by fn_trace_gettable. I do understand that we will have to wait until trace file rolls over to get the information but it will be logged anyway. But in general, this article is a very good and easy to understand example on how to use the Service Broker.
Regards, Yelena Varshal
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Monday, September 07, 2009 3:56 AM
Points: 116,
Visits: 46
|
|
Yelena,
You are right, you can get the same results by running a trace and saving it to a table. However I think event notifications are more robust and more flexible.
I named the article SQL Server Logon triggers, refering to Oracle. But on SQL Server they are not really triggers.
Kind regards,
Frederik
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Wednesday, January 17, 2007 9:44 AM
Points: 2,
Visits: 1
|
|
| but how to you disable unwanted logon like determine who from which workstation using which program. service broker will not kill the other one right?
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Monday, September 07, 2009 3:56 AM
Points: 116,
Visits: 46
|
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Friday, October 30, 2009 11:09 AM
Points: 6,
Visits: 122
|
|
Can you please upload the scripts again. I can't find them under the URL you have listed. thanks
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Monday, October 12, 2009 3:29 AM
Points: 4,
Visits: 34
|
|
Thanks for your offer this article.
I met some errors after running your scripts; ERRORLOG memessageike this...
... 2009-02-27 17:07:35.01 spid14s Error: 9644, Severity: 16, State: 14. 2009-02-27 17:07:35.01 spid14s An error occurred in the service broker message dispatcher, Error: 15517 State: 1.
and I could resolve this problem for below code, from http://social.technet.microsoft.com/Forums/en-US/sqlservicebroker/thread/a5af6e9a-f3b6-4b73-ae3d-95238502d28f/
ALTER AUTHORIZATION ON DATABASE::[My_DB_Name] TO [SA];
It works fine. I like it. :)
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Tuesday, November 03, 2009 11:37 AM
Points: 42,
Visits: 64
|
|
Hi,
Did you implement this logon trigger in a production heavy environment ?
I am asking this because i read few articles about big problems with logon triggers in heavy environments, sql instances crushes few times.
|
|
|
|