[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

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.
1 Solution

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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now