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

Question

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

RCRock

Right there with Babe

Points: 718
More actions
August 13, 2019 at 2:56 pm

#3671051

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.

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

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

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: 734434 More actions · Answer 2

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 Right there with Babe Points: 718 More actions · Answer 3

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: 734434 More actions · Answer 4

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.