Logging user access to a sql express database

I have an application that uses sql express 2005. I need to have a method to log anytime someone uses the Management console to log into the database. Ideally, I would like to be able to log what changes they made as well.

Does anyone know if either is possible? If so, how to go about it.
Who is Participating?
You can of course log event when someone uses the management studio to login:
Run this:
SELECT spid,login_time,[program_name],db_name([dbid]),uid,loginame,hostname,cmd,nt_domain,nt_username,net_address
FROM sys.sysprocesses
WHERE [program_name] LIKE 'Microsoft SQL Server Management Studio%'

Now to be able to log this you can create a LOGON trigger which will be fired any time a user will connect using SSMS.

The best way to implement and track all this is to use the EVENDATA function and use a TRIGGER to record all the event related to a user,including all the queries (DROP,UPDATE,etc...) he executed .

Will post you such a trigger very soon...

The following would give you some idea, but may not give you the exact details.
select * from sys.dm_exec_connections;
select * from sys.dm_exec_sessions;
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.