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

x
  • 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.
0
dgerler
Asked:
dgerler
1 Solution
 
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
 
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

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