Post reply

Login History - who connected to a database?

  • June 13, 2013 at 4:22 am

    #273821

    Hello people,

    is there a way that we can display all the people who have connected to a certain database over a given time? For instance, we have a read-only user name for people wishing to connect to our live database, in the normal course of our day to day activity. All the users also have a read-write access, but we would like to audit the times that people are connecting with this read-write access - this is independent of any actual changes to the routines that they may make while logged in.

    Is the information stored anywhere in SQL server management studio, from which we would be able to make a report or similar to show all logins, with time and duration?

    Thanks,

    Mark

  • June 13, 2013 at 5:47 am

    #1623632

    Hi MARK,

    I know SQL is having such power inorder to trace out the users, who accesssed to the DB;

    As far as i know it is called to be "Profile Tracer", I am not a such a expert to guide in means of code,

    I just wanted to give you the IDEA.

    There is another service in SQL inorder to track the changes done to the database; you can do this by enabling "CDC (Change Data Capture)" This just record what kind of changes ("Insert, Delete, Update") are done, But doesnt record who did that.

    So follow according to your need,

    Anyway there are many PRO's here to help you, Hope we get perfect answer unlike mine.

    Thanks,

    RAJ :hehe:

  • June 13, 2013 at 6:15 am

    #1623653

    rajborntodare is correct;

    the information is not saved anywhere on SQL server, unless you explicitly added one of the many audit options available to SQL server.

    so going forward, I would recommend adding an extended event to start capturing the specifics, or maybe a server side trace

    a trace is probably easier to set up, as extended events , even for me, are a little more difficult without a copy/paste example.

    the extended events have two distinct advantages though: As I understand an explanation form Grant Fitchey,

    a trace captures data, THEN applies any filters you may have added;

    extended_events apply the filter FIRST,then captures the data, resulting in a lower impact on the server.

    The second thing is a trace is stopped whenever the SQL service stops; you have to recreate the trace(usually by calling a procedure at startup) to recreate the trace.

    extended events use the service broker, and don't get stopped automatically like that.

    there are other options like enabling the built in C2 Level auditing (which is really a server side trace), SQL Audit, SQL Trace, and SQL Change Data Capture, but some of those items require the enterprise versions.

    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!

  • June 13, 2013 at 6:24 am

    #1623660

    Thanks guys,

    I have had a little look at the profiler and that looks as though it will do the job for now, but as you say, it seems to record a lot of information. I'll look into the extended events audit later.

    It looks as though both of these options will need to be discussed in the team but both can supply what we want; thanks also for the explanation about the filtering!

    Cheers,

    Mark

  • June 13, 2013 at 8:39 am

    #1623748

    HI Guys,

    I just turned back to give you a little information which helped me in enabling CDC @ my Production servers,

    As i got a task couple weeks back enabling CDC; I refered this link

    https://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-%28cdc%29-in-sql-server-2008/

    he gave a better idea to understand enabling CDC.

    Just a bit of information:

    Thanks,

    RAJ :hehe:

Viewing 5 posts - 1 through 4 (of 4 total)

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