How to get the list of login accounts logged in over specific period of time

  • RCRock

    Say Hey Kid

    Points: 672

    Hi,

    Can any one help me to give me the sql script for following requirement

    1: List of login account stored in DB table for last 3 months against a specific DB like TestDB

    2: The script should insert the records to table with following details

    Login name, Login DateAndTime, Database Name,Host name etc

    for example if any account testaccount logged in weekly to connect TestDB , then our script must log in that information as well for past week login details too into DB Table.

     

     

  • Lowell

    SSC Guru

    Points: 323441

    Unfortunately, unless you have set something up previously, this is not possible. No default setting captures that level of information, as the log would probably exceed the database size.

    also, no-one "logs into" a database. at the time of initial login, you can declare a default database(ie master)

    you can capture that, but it's not always useful.

    once you have connected when you execute USE OtherDatabase or do query a specific database like select * from MyCompany.dbo.Invoices, that is not captured at all.

    so it depends on what you are trying to capture.

    if you are trying to capture just plain old usage, like when was the last tiem a table was selected from or updated, you can get some of that information form index stats.

    if you want login information for whodunnit information, like what login is doing SELECT, you need an extended event or a trigger to audit that kind of stuff.

    If you are trying to capture exactly WHAT rows and columns someone selected, it's a real, real difficult operation. probably impossible.

     

     

    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!

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716540

    1. I'm not sure what you are looking for here. This doesn't quite make sense in English.

    2. As Lowell mentioned, you can get some of this information with Extended Events or SQL Audit. You can also use a DDL trigger, like this

    https://www.sqlservercentral.com/forums/topic/creating-logon-logoff-triggers-in-mssql

  • RCRock

    Say Hey Kid

    Points: 672

    I am using Sql Server 2016

    My main requirement is to store the login account information , date and time into sql table whenever any one login to specific Database.

    If the information are stored in table, then I can use this table to extract the information whenever I need it.

     

    I can check on the trigger but not sure if it will capture the Database level information.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716540

    If someone logs into the SQL Server instance, and their context is master, or say db_a. If they execute a "Use db_b", the trigger won't capture this. In fact, not sure what will capture the context change.

    Keep in mind that you're asking for getting a note when someone connects to a database. That's not a login. A login is to the instance. Your login event can connect to a specific database, but it could also be the result of a USE statement (like changing the dropdown in SSMS). If you don't care about those, just the initial login, then the DDL trigger will capture the login.

    Anything in a database is a user, not a login as well, so are you looking for information on users or logins? They are different things.

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

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