Post reply

Connections made to Server

  • February 22, 2019 at 10:08 am

    #373907

    Is there a way to identify how many hits were made by particular user against a database server? If yes,is it possible to get those details from few days ago?

    Thanks

  • February 22, 2019 at 12:09 pm

    #2022687

    Admingod - Friday, February 22, 2019 10:08 AM

    Is there a way to identify how many hits were made by particular user against a database server? If yes,is it possible to get those details from few days ago?

    Thanks

    You would need to set up some type of auditing for this. If you had enabled auditing for successful logins in the server properties, you could get their logins.
    But if not and nothing is setup to audit the user, you wouldn't know. 

    Sue

  • February 22, 2019 at 12:55 pm

    #2022697

    Thanks Sue! There is no audit configured. Are saying if i configure the server level trigger would that audit all the hits from the users against particular database?

  • February 22, 2019 at 1:04 pm

    #2022698

    Admingod - Friday, February 22, 2019 12:55 PM

    Thanks Sue! There is no audit configured. Are saying if i configure the server level trigger would that audit all the hits from the users against particular database?

    Not a trigger. In the server properties, you can enable auditing or failed logins, successful logins or both. Once you look, you'll know what I am talking about. In SSMS, right click on the instance and select the Security page on the left. In the middle you set the basic login auditing.
    But on that one, it has nothing to do with any particular database. It's just logins.

    Sue

  • February 22, 2019 at 1:16 pm

    #2022699

    Thanks! I can see the auditing option as failed logins enabled. That is the reason the SQL Server error log has failed logins activity? However, i am still trying to understand how this would be related to the data the total hits and connections made by the user? Just curios previously you have responded to my other questions. Wondering you own any blogs?

  • February 22, 2019 at 1:57 pm

    #2022709

    it sounds much more like you are looking for whodunnit information...like did Lowell access the Payroll database.
    just a plain old login audit will only tell you Lowell connected 40 times on a certain day, and what his default database was....not did Lowell do select * from Payroll.dbo.CompanySalaries or delete anything from Company.dbo.OfficialReprimands
    that requires another layer of auditing.

    like Sue_H said though, the info can be captured, but you need to have that capture stuff in place first....there's no way to see prior activity by person without something in place.

    what specifically, do you need to capture? who updated a table? who deleted a row?
    did someone backup a database to a flash drive?

    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!

  • February 22, 2019 at 2:49 pm

    #2022720

    Admingod - Friday, February 22, 2019 1:16 PM

    Thanks! I can see the auditing option as failed logins enabled. That is the reason the SQL Server error log has failed logins activity? However, i am still trying to understand how this would be related to the data the total hits and connections made by the user? Just curios previously you have responded to my other questions. Wondering you own any blogs?

    Yes, that's where the failed logins you see in the error log come from. It captures just logins. Someone could log in and do nothing. Or they could log in and go all over the place. Total hits and you mentioned particular databases leads me to think you mean something other than just logins.
    Are you wanting to capture more about one particular login and what they are doing? 
    And no I don't have a blog. If I did, I'd be the only one reading. And if others found it, I'm afraid I'd spent so much time defending or explaining why or too much time going through all the "better" ways to do whatever that I just wouldn't want to do it anymore. 🙂

    Sue

  • February 23, 2019 at 1:02 pm

    #2022743

    Thanks. That answer my failed logins question. I never realized error log entry is happening through failed login settings.
    Yes, i am looking for total hits against user on a particular database not just logins. The way i look at it users can have multiple connections, so i want to capture total connections made at a particular time by that user against a database. I am thinking it wont be possible other than monitoring tool in place?

  • February 25, 2019 at 10:41 am

    #2022865

    Admingod - Saturday, February 23, 2019 1:02 PM

    Thanks. That answer my failed logins question. I never realized error log entry is happening through failed login settings.
    Yes, i am looking for total hits against user on a particular database not just logins. The way i look at it users can have multiple connections, so i want to capture total connections made at a particular time by that user against a database. I am thinking it wont be possible other than monitoring tool in place?

    You'd need to set up some type of auditing or use a monitoring tool of some sort. Some of it would depend on what you want to monitor. If you just wanted to track the users DML activity in a database (select, insert, update, delete), execution of stored procedures than setting up a database audit would work. This article goes through all of the different auditing options built into SQL Server. The section called SQL Server Auditing is the database audit I mentioned: 
    Creating a successful auditing strategy for your SQL Server databases

    Sue

  • February 25, 2019 at 12:29 pm

    #2022893

    Sue_H - Friday, February 22, 2019 2:49 PM

    Yes, that's where the failed logins you see in the error log come from. It captures just logins. Someone could log in and do nothing. Or they could log in and go all over the place. Total hits and you mentioned particular databases leads me to think you mean something other than just logins. 

    Are you wanting to capture more about one particular login and what they are doing? 
    And no I don't have a blog. If I did, I'd be the only one reading. And if others found it, I'm afraid I'd spent so much time defending or explaining why or too much time going through all the "better" ways to do whatever that I just wouldn't want to do it anymore. 🙂

    Sue

    I'd read it, I've found over the years you have quite a number of useful things to say. 🙂  Me, I just don't have time.

  • February 25, 2019 at 2:04 pm

    #2022917

    Chris Harshman - Monday, February 25, 2019 12:29 PM

    Sue_H - Friday, February 22, 2019 2:49 PM

    Yes, that's where the failed logins you see in the error log come from. It captures just logins. Someone could log in and do nothing. Or they could log in and go all over the place. Total hits and you mentioned particular databases leads me to think you mean something other than just logins. 

    Are you wanting to capture more about one particular login and what they are doing? 
    And no I don't have a blog. If I did, I'd be the only one reading. And if others found it, I'm afraid I'd spent so much time defending or explaining why or too much time going through all the "better" ways to do whatever that I just wouldn't want to do it anymore. 🙂

    Sue

    I'd read it, I've found over the years you have quite a number of useful things to say. 🙂  Me, I just don't have time.

    If you'd actually read it, that explains why you don't have time 🙂

    But seriously - thanks for the nice words,

    Sue

Viewing 11 posts - 1 through 10 (of 10 total)

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