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.
dgerlerAsked:
Who is Participating?
 
spikellyCommented:
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...
0
 
sameer2010Commented:
Hi,

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;
0
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.