Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2005 Logon Triggers


SQL Server 2005 Logon Triggers

Author
Message
F Vandeputte
F Vandeputte
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 59
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/FVandeputte/sqlserver2005logontriggers.asp
Yelena Varshal
Yelena Varshal
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3700 Visits: 594

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

F Vandeputte
F Vandeputte
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 59
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
alan ding
alan ding
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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?
F Vandeputte
F Vandeputte
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 59
Alan,

SQL Server 2005 SP2 CTP was released last week. MS added logon triggers. This will help you with problem.

See my follow up post on my blog

http://www.vandeputte.org/2006/11/sql-server-logon-triggers-part-2.html

cnappoly
cnappoly
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 191
Can you please upload the scripts again. I can't find them under the URL you have listed.
thanks



tsohr
tsohr
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 45
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. Smile
Victor Shahar
Victor Shahar
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 248
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.
sgambale
sgambale
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 265
I implemented the event notification fo rlogin as in Frederick's article.
It work fine.

Now I want to only insert rows for certain loginname's.

I made another stored procedure with an IF statement in the stored proc that only inserts if the loginname is not in a list that I provide. If it is, I roll back and break.
I alter queue with status = off for the old stored proc, then alter queue with status = on fo rthe new proc.

What happens is I get one row that stays in the queue when I let someone log in that should be INSERTED into the table and no further INSERTS occur.

This is the new stored proc:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[proc_log_user_logins_new]
AS
SET NOCOUNT ON;

DECLARE @message_body XML,
@message_type_name NVARCHAR(256),
@dialog UNIQUEIDENTIFIER ;

--Endless loop
WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION ;

-- Receive the next available message

WAITFOR (
RECEIVE TOP(1)
@message_type_name=message_type_name,
@message_body=message_body,
@dialog = conversation_handle
FROM log_user_logins_queue
), TIMEOUT 2000

--Rollback and exit if no messages were found
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION ;
BREAK ;
END ;

--End conversation of end dialog message
IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
PRINT 'End Dialog received for dialog # ' + cast(@dialog as nvarchar(40)) ;
END CONVERSATION @dialog ;
END ;
ELSE

IF CAST(@message_body.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100))
NOT IN ('m58467','ITSERVICES\M10077','patrol_ssuser','patrol_ssadmin')
BEGIN
ROLLBACK TRANSACTION ;
BREAK ;
END ;
BEGIN
INSERT INTO log_user_logins (
EventTime,
EventType,
LoginName,
HostName,
NTUserName,
NTDomainName,
Success,
FullLog )
VALUES
(
CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS VARCHAR(64)) AS DATETIME),
CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/HostName/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/NTUserName/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/NTDomainName/text()') AS VARCHAR(100)),
CAST(CAST(@message_body.query('/EVENT_INSTANCE/Success/text()') AS VARCHAR(64)) AS INTEGER),
@message_body)
END

COMMIT TRANSACTION
END


Any help would be greatly appreciated.
I am not sure how to properly code rows I receive , but do not want to insert or keep them in the queue.

The row in the queue:
select * from log_user_logins_queue

1 0 13 D19B6C5A-C927-DF11-9A25-001A64C552F2 D29B6C5A-C927-DF11-9A25-001A64C552F2 6 log_user_logins_service 65539 http://schemas.microsoft.com/SQL/Notifications/PostEventNotification 2 http://schemas.microsoft.com/SQL/Notifications/EventNotification 4 X 0xFFFE3C004500560045004E0054005F0049004E005300540041004E00430045003E003C004500760065006E00740054007900700065003E00410055004400490054005F004C004F00470049004E003C002F004500760065006E00740054007900700065003E003C0050006F0073007400540069006D0065003E0032003000310030002D00300033002D00300034005400310032003A00330032003A00310032002E003100380030003C002F0050006F0073007400540069006D0065003E003C0053005000490044003E00360032003C002F0053005000490044003E003C00540065007800740044006100740061003E002D002D0020006E006500740077006F0072006B002000700072006F0074006F0063006F006C003A0020005400430050002F0049005000260023007800300044003B000A007300650074002000710075006F007400650064005F006900640065006E0074006900660069006500720020006F006E00260023007800300044003B000A00730065007400200061007200690074006800610062006F007200740020006F0066006600260023007800300044003B000A0073006500740020006E0075006D0065007200690063005F0072006F0075006E006400610062006F007200740020006F0066006600260023007800300044003B000A00730065007400200061006E00730069005F007700610072006E0069006E006700730020006F006E00260023007800300044003B000A00730065007400200061006E00730069005F00700061006400640069006E00670020006F006E00260023007800300044003B000A00730065007400200061006E00730069005F006E0075006C006C00730020006F006E00260023007800300044003B000A00730065007400200063006F006E006300610074005F006E0075006C006C005F007900690065006C00640073005F006E0075006C006C0020006F006E00260023007800300044003B000A00730065007400200063007500720073006F0072005F0063006C006F00730065005F006F006E005F0063006F006D006D006900740020006F0066006600260023007800300044003B000A00730065007400200069006D0070006C0069006300690074005F007400720061006E00730061006300740069006F006E00730020006F0066006600260023007800300044003B000A0073006500740020006C0061006E00670075006100670065002000750073005F0065006E0067006C00690073006800260023007800300044003B000A007300650074002000640061007400650066006F0072006D006100740020006D0064007900260023007800300044003B000A00730065007400200064006100740065006600690072007300740020003700260023007800300044003B000A0073006500740020007400720061006E00730061006300740069006F006E002000690073006F006C006100740069006F006E0020006C006500760065006C0020007200650061006400200063006F006D006D0069007400740065006400260023007800300044003B000A003C002F00540065007800740044006100740061003E003C00420069006E0061007200790044006100740061003E0049004100410041004B0044006A003000410051004100410041004100410041003C002F00420069006E0061007200790044006100740061003E003C0044006100740061006200610073006500490044003E0036003C002F0044006100740061006200610073006500490044003E003C004E00540055007300650072004E0061006D0065003E007300670036003100350038003C002F004E00540055007300650072004E0061006D0065003E003C004E00540044006F006D00610069006E004E0061006D0065003E00490053004F003C002F004E00540044006F006D00610069006E004E0061006D0065003E003C0048006F00730074004E0061006D0065003E004E004A004300440054004C00300031005300470036003100350038003C002F0048006F00730074004E0061006D0065003E003C0043006C00690065006E007400500072006F006300650073007300490044003E0032003900340034003C002F0043006C00690065006E007400500072006F006300650073007300490044003E003C004100700070006C00690063006100740069006F006E004E0061006D0065003E004D006900630072006F0073006F00660074002000530051004C00200053006500720076006500720020004D0061006E006100670065006D0065006E0074002000530074007500640069006F0020002D002000510075006500720079003C002F004100700070006C00690063006100740069006F006E004E0061006D0065003E003C004C006F00670069006E004E0061006D0065003E00490053004F005C007300670036003100350038003C002F004C006F00670069006E004E0061006D0065003E003C0053007400610072007400540069006D0065003E0032003000310030002D00300033002D00300034005400310032003A00330032003A00310032002E003100370037003C002F0053007400610072007400540069006D0065003E003C004500760065006E00740053007500620043006C006100730073003E0031003C002F004500760065006E00740053007500620043006C006100730073003E003C0053007500630063006500730073003E0031003C002F0053007500630063006500730073003E003C0049006E007400650067006500720044006100740061003E0034003000390036003C002F0049006E007400650067006500720044006100740061003E003C005300650072007600650072004E0061006D0065003E004300410048005900570052003100430041005000490054004C00300036003C002F005300650072007600650072004E0061006D0065003E003C00440061007400610062006100730065004E0061006D0065002F003E003C004C006F00670069006E005300690064003E0041005100550041004100410041004100410041005500560041004100410041005700540048004C0064003400390051003400330043007A005A005100310058003200690051004100410041003D003D003C002F004C006F00670069006E005300690064003E003C005200650071007500650073007400490044003E0030003C002F005200650071007500650073007400490044003E003C004500760065006E007400530065007100750065006E00630065003E003200370036003000320035003C002F004500760065006E007400530065007100750065006E00630065003E003C0049007300530079007300740065006D002F003E003C00530065007300730069006F006E004C006F00670069006E004E0061006D0065002F003E003C002F004500560045004E0054005F0049004E005300540041004E00430045003E00

I think the problem is what is stated in books online for receive statement:

"The RECEIVE statement removes received messages from the queue unless the queue specifies message retention. When the RETENTION setting for the queue is ON, the RECEIVE statement
updates the status column to 1 and leaves the messages in the queue. When a transaction that contains a RECEIVE statement rolls back, all changes to the queue within the transaction are also rolled back, returning messages to the queue."

my retention is the default (off).
manoj.ks
manoj.ks
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 207
The script is working fine. But the table is keep on updating without any new logon event occurs. Also, it is not working for add_role_member server event.

Any help in this?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search