Logging user access to a sql express database

Posted on 2009-12-21
Last Modified: 2012-05-08
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.
Question by:dgerler
    LVL 13

    Expert Comment


    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;
    LVL 5

    Accepted Solution

    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

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
    This video discusses moving either the default database or any database to a new volume.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now