Post reply

Track all logins on to a single SQL database

  • October 17, 2014 at 4:17 am

    #297526

    Thanks in advance for any help.

    We have a particular database sat on SQL Server 2012 box along with about 20 other databases.

    What I require is a method/Script/Audit (Open minded about the solution) that will simply track anyone who logs (successfully / unsuccessful) into this one particular database on the server (The single database is the key as the end user does not want information on any of the other databases that sit on the server), it also has to log time the attempt was made and it must track the logins via SQL Server or the application itself that is attached to the database.

    Once we have this information we need to simply store that somehow. I say somehow as the storing part depends on the solutions recommended to me, so I’m open minded about this too.

    Any help would be great as I’m scratching my head on this one.

  • October 17, 2014 at 10:37 am

    #1753215

    you've got to differentiate between LOGIN and ACCESS

    a login occurs once, and might have a reference a database in it's connection string, but it never "logs into" a database.

    After you connect, and then you might ACCESS the that default databases in it's connection string, or different databases, and can access stuff hundreds of times after that connection is established.

    so do you want to track success/failed logins, or the commands someone issued after a successful connection?to data?

    for commands,

    you might want to use SQL Audit, Extended Events or a server side trace are the first three things that come to mind for tracking access, but it depends a lot on the details of the full requirement;

    auditing can eat up a lot of space, so be aware.

    if your application connected with a common password, you cannot trace it back to the user that used the application, unless the applciaiton itself is logging that information explicitly somewhere;

    so if everyone connects as "appuser", you can't know whether it was Bob or Bill who was using the app .

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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