June 16, 2008 at 9:18 am
Hello,
Can you guys help me out with a way to capture Login/Logout activity on a sql server?
We need to track and store the output when a user logged in and logged out to the database.
Thanks In Advance!
June 16, 2008 at 9:26 am
Database or server? Database is impossible without tracking every SQL statement that goes through. Server is possible through a server trace. You can build that using SQL Profiler. If you build the trace and put it into a stored procedure, you can use sp_procoption to automatically start that stored procedure every time SQL Server starts. But make sure you plan roll-over, etc.
K. Brian Kelley
@kbriankelley
June 16, 2008 at 11:06 am
Users don't log into a database. They log into a server (SQL Server instance) and access a database. As Brian mentioned, only tracking every query would tell you when they've accessed the database.
If you want to track server logins, use a server side trace, start it automatically as Brian mentioned, and store it on a drive with space. Be sure you monitor the files and archive/delete old ones.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 16, 2008 at 11:13 am
Brian/Steve,
Sorry i meant to say server.
So by doign a server trace we can also get the dabaseid or name..right.
Will this be a lot of overhead on the server.
Please confirm
June 16, 2008 at 11:22 am
In SQL Server 2000 you can get the database ID. If you restrict it to just login/logout and minimize the columns to only what you need, it probably won't have a noticeable impact on performance.
K. Brian Kelley
@kbriankelley
June 16, 2008 at 11:30 am
Guys,
Thanks Very Much !
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply