September 21, 2010 at 3:00 pm
Hi
I am a rookie DBA in SQL Server. I have the objetive of recording in a table the following information: user, machine & time for each login to SQL Server. In Oracle this can be done through a Logon trigger but I do not know how to do it in sql server. Can you help me ?
Thanks
September 21, 2010 at 3:13 pm
Logon triggers exist in SQL Server as well:
http://msdn.microsoft.com/en-us/library/bb326598(v=SQL.100).aspx
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 21, 2010 at 3:16 pm
As a DBA you should have access to BOL (Books on Line) the help guide for SQL sever. Under Triggers you will find:
Logon triggers fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established. Therefore, all messages originating inside the trigger that would typically reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL Server error log. Logon triggers do not fire if authentication fails.
If yoou do not have access to BOL use this link to get started
http://technet.microsoft.com/en-us/library/bb326598.aspx
To obtain the user look up in BOL USER_NAME
September 21, 2010 at 3:30 pm
Hi I checked the links and now I have the information for creating the trigger bu I need still the global variables to record:
User Connected ( I going to use these: ( SYSTEM_USER, CURRENT_USER ) )
but I need still the global variables for identify:
1.- The machine where the connection is coming and
2.- The current time for that moment.
Can you help me with that information ?
Regards
September 21, 2010 at 6:17 pm
machine name SELECT host_name()
2.- The current time for that moment
use GETDATE()
Again learn how to use Books On Line the SQL Server help file
It is readily available when using SSMS.
September 22, 2010 at 8:22 am
Excelent getdate and host_name functioned. Now I need the global variable to get the IP address of the host_name.
Thanks in advance
September 22, 2010 at 1:31 pm
Hi
Finally I am trying to create this trigger but I am getting an error... I am working in SQL Server 2005 but really the trigger is going to be created in SQL Server 2000.
create trigger dbo.trigger_login_registers
on database
for logon
as
BEGIN
insert into dbo.login_registers values
(SYSTEM_USER, CURRENT_USER, getdate(), host_name());
END;
The error is the following:
Msg 1084, Level 15, State 1, Procedure trigger_login_registers, Line 3
'logon' is an invalid event type.
I am checking the sintaxis and according to it, All is fine...
Thanks
September 22, 2010 at 1:59 pm
jfrancisco
Finally I am trying to create this trigger but I am getting an error... I am working in SQL Server 2005 but really the trigger is going to be created in SQL Server 2000.
I believe that logon triggers were first allowed in SQL 2005 SP 2,
any SQL Server before that does NOT support logon triggers.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply