May 30, 2002 at 7:20 am
Anybody knows if MSSQL server provides a way to log every login to the server so I can review the log and know when every user has logged in the server?
Thanks in advance
May 30, 2002 at 7:53 am
I have never seen anything that SQL logins can be tracked with other than using Profiler.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 30, 2002 at 9:03 am
Is there a way to accomplish this?
Thanks again
May 30, 2002 at 9:14 am
Profiler is the best. The only alternative would be to gate everythrough a stored procedure that writes to a log. You'd have to retrofit all your existing apps to call the proc on start up (or whereever is appropriate).
Just because Im curious, why do you care?
Andy
May 30, 2002 at 9:49 am
A frien of mine asked me the question. It looks that they want to track an employee's logging in and out times. I told him that the best solution would be a trigger that adds a record to a log table for insert/update/delete but I thought that MSSQL might have a way that I did not know.
Thanks again
May 30, 2002 at 9:53 am
What version of SQL are they running, I have found a way that will work.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 30, 2002 at 9:53 am
Sorry for the double post, I forgot to explain my idea
The recommendation for the trigger would to create a table in the same database with 3 fields, user name (SYSTEM_USER) and datetimelogin GETDATE() and datetimelastmodif GETDATE() that would add/update a record (if it does not exist for that day at login and update for every update/delete).
Do you see this as a good solution?
TIA
May 30, 2002 at 9:54 am
Sorry for the double post, I forgot to explain my idea
The recommendation for the trigger would to create a table in the same database with 3 fields, user name (SYSTEM_USER) and datetimelogin GETDATE() and datetimelastmodif GETDATE() that would add/update a record (if it does not exist for that day at login and update for every update/delete).
Do you see this as a good solution?
TIA
May 30, 2002 at 9:55 am
I don know exactly but I am assuming that it is SQL2000 or as a minimum 7.0
May 30, 2002 at 10:25 am
Ok here it is kind of ruff but you should be able to follow.
SQL 7
declare @P1 int
declare @P2 int
set @P1=32|64|512|1024|8192 --Column output selection list
/*
32 Microsoft Windows NT® username
64 Windows NT domain name
512 Application name
1024 SQL username
8192 Start time
*/
set @P2=0 --Default QueueHandle to 0
exec xp_trace_addnewqueue 1000, 5000, 95, 90, @P1, @P2 output, 1
select @P2 --YOu will need this output number for destroy queue otherwise you will have to shutdown server to stop trace
--Capture Events
exec xp_trace_seteventclassrequired @P2, 14, 1 --Connect
exec xp_trace_seteventclassrequired @P2, 15, 1 --Disconnect
exec xp_trace_seteventclassrequired @P2, 20, 1 --LoginFailed
--Configure the queue to write to a file.
EXEC xp_trace_setqueuedestination @P2, 2, 1, NULL, 'c:\AuditLogins.trc'
--Start the consumer that actually writes to a file.
EXEC xp_trace_startconsumer @P2
--Save the queue definition to the server.
--EXEC xp_trace_savequeuedefinition @P2, 'AuditLogins', 1
--Mark it for autostart on the server’s next restart. Use 1 for yes, 0 for no in secound parameter
--EXEC xp_trace_setqueueautostart 'AuditLogins', 1
--This is how you drop the queue
--EXEC xp_trace_destroyqueue @P2
-----------------------------------------------------------------------------------------------------------------------
SQL 2000
declare @P1 int
set @P1=0
exec sp_trace_create @P1 output, 2, N'C:\LoginAudit', NULL, NULL
select @P1 --You will need this output to be able to stop trace otherwise shutdown SQL.
--@x is a bit value, for some reason you must submit parameter 4 this way to get to work properly.
declare @x bit
set @x = 1
--Login
exec sp_trace_setevent @P1,14,11, @x
exec sp_trace_setevent @P1,14,14, @x
--Logout
exec sp_trace_setevent @P1,15,11, @x
exec sp_trace_setevent @P1,15,14, @x
--LoginFailed
exec sp_trace_setevent @P1,20,11, @x
exec sp_trace_setevent @P1,20,14, @x
--Start trace
exec sp_trace_setstatus @P1, 1
/*
--Parameter 1 is the trace id to stop and remove from queue
exec sp_trace_setstatus @P1,0
exec sp_trace_setstatus @P1,2
*/
Look at SQL BOL for the relative information of each process.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 30, 2002 at 12:05 pm
Wow! Thanks a lot. Great source.
Thanks again
May 30, 2002 at 12:40 pm
Change the Audit Level to "Success" or "All" in the SQL Server properties Security tab. All logins to SQL Server will be logged.
May 31, 2002 at 1:52 am
I agree with Aragorn, from sql properties you can set audit level. It is a built in function, why use anything else.
Pargat
May 31, 2002 at 2:48 am
You're right. It's one of those things I don't use and forgot about! Had to look back to see how it worked - have to stop/start svc when you enable it (gee, thats fun) and it writes to the error log and/or the event application log. Only down side I can see is that if you have a large volume to track neither place is really a good place to log it. Has the merit of simplicity!
Andy
May 31, 2002 at 4:40 am
I also forgot, figures, don't use something long enough and you totally forget about it. However the one advantage with what I put out will give you is you can turn it on and off without shutting down SQL Server for the change to take effect. Also you can include disconnects (but those seem a little strange if you look you get extra disconnects and connections not login/logoff with SQL 7 but I am still playing with that but I believe you find the first connect/disconnect and next and that is what SQL is monitoring as Login/Logout in 2000 profiler.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply