Post reply

Audit Logins in SQL Server 2005

  • September 14, 2010 at 8:45 am

    #229287

    Hello All,

    I need some help to know a way to collect the users that have logged in to an instance and what the specified user have change on the database.

    There is any way on how to do this on SQL Server 2005? If yes, how can i do it?

    Thanks in advance for the help

    Ângelo Silva

  • September 14, 2010 at 9:13 am

    #1220768

    in SQL 2005 you can change the logging options to log all successful and un-successful logins by click on the security tab under database properties.

    To see who is currently logged in, you can run

    sp_who2

    to see what they have changed depends on whether you mean data changes or structure changed. For structure changes you may be able to see this in the default trace.

    for data changes you will need to create this possibly by using triggers, however it really depends on your system. There is nothing by default that records who changes what data

  • September 14, 2010 at 9:53 am

    #1220793

    Yes I know that.

    What I need to do is when a user login to the instance collect that information to a table and when the user change something on the structure like login permissions, collations, database version, etc. Collect this information to a table as well.

    I take a look on DDL Triggers but there is nothing that do something like this. What I want to do is keep the information for future analisys.

    Can you give some help on this.

    Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply