Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Login History - who connected to a database? Expand / Collapse
Author
Message
Posted Thursday, June 13, 2013 4:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 1:53 AM
Points: 34, Visits: 108
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
Post #1462968
Posted Thursday, June 13, 2013 5:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 10:12 AM
Points: 110, Visits: 501
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
Post #1462992
Posted Thursday, June 13, 2013 6:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:38 AM
Points: 12,910, Visits: 32,021
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1463014
Posted Thursday, June 13, 2013 6:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 1:53 AM
Points: 34, Visits: 108
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
Post #1463022
Posted Thursday, June 13, 2013 8:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 10:12 AM
Points: 110, Visits: 501
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
Post #1463130
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse