Capture login information

Question

Post reply

Capture login information

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
September 17, 2021 at 12:55 pm

#3930527

I want to Capture Login information in table or view. who connected to database and when they connected.
looking for script
September 20, 2021 at 7:03 am

This was removed by the editor as SPAM

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

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

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

homebrew01

SSC Guru

Points: 55548

September 17, 2021 at 1:31 pm

#3930560

SQL Trace, or newer tool, Extended Events.

Adi Cohn SSC-Addicted Points: 424 More actions · Answer 2

Adi Cohn

SSC-Addicted

Points: 424

September 19, 2021 at 11:36 am

#3930944

Could also check logon trigger.

Adi

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 3

Here's a link about things that you can to do with login triggers and the like. One of the things is to audit logins.

https://www.sqlshack.com/an-overview-of-logon-triggers-in-sql-server/

Note that if you're doing this to support anything legal, it won't stand up in court because people with sys.admin privs can change it. For that, a certified login audit package should be purchased.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

Here's another one, by Jack Worthen. It's a bit more to the point.

https://jackworthen.com/2018/12/20/creating-a-logon-trigger-to-track-when-a-specific-account-is-connecting-to-a-sql-server/

Here's a "leader" article on the subject from MS.

https://docs.microsoft.com/en-us/sql/relational-databases/triggers/logon-triggers?view=sql-server-ver15

I'm finding all of this via Google. You can too! 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 5

I Have created table along with trigger but the problem is its logging all system events. I want only the users accounts who are logging into the database (DataMap)

Step -1

CREATE TABLE Audit_Logins

(Login_Name NVARCHAR(256),

Login_Time DATETIME);

---create the logon trigger with the following script.

CREATE TRIGGER TR_Audit_logins ON ALL SERVER

FOR LOGON

AS

BEGIN

INSERT INTO DataMap.dbo.Audit_Logins

SELECT ORIGINAL_LOGIN(),

GETDATE()

END;

I tried to create trigger like below on only database but its throwing error - The specified event type(s) is/are not valid on the specified target object.

CREATE TRIGGER TR_Audit_logins ON DataMap

FOR LOGON

AS

BEGIN

INSERT INTO DataMap.dbo.Audit_Logins

SELECT ORIGINAL_LOGIN(),

GETDATE()

END;

All i want to log the current user and the date when they connected to DataMap Database.

homebrew01 SSC Guru Points: 55548 More actions · Answer 6

How about

INSERT INTO DataMap.dbo.Audit_Logins
SELECT ORIGINAL_LOGIN(), GETDATE()
where db_name() = 'DataMap'

This reply was modified 3 years, 2 months ago by homebrew01.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 7

mcfarlandparkway wrote:

I Have created table along with trigger but the problem is its logging all system events. I want only the users accounts who are logging into the database (DataMap)
Step -1
CREATE TABLE Audit_Logins (Login_Name NVARCHAR(256), Login_Time DATETIME);
---create the logon trigger with the following script.
CREATE TRIGGER TR_Audit_logins ON ALL SERVER FOR LOGON AS BEGIN INSERT INTO DataMap.dbo.Audit_Logins SELECT ORIGINAL_LOGIN(), GETDATE() END;
I tried to create trigger like below on only database but its throwing error - The specified event type(s) is/are not valid on the specified target object.
CREATE TRIGGER TR_Audit_logins ON DataMap FOR LOGON AS BEGIN INSERT INTO DataMap.dbo.Audit_Logins SELECT ORIGINAL_LOGIN(), GETDATE() END;
All i want to log the current user and the date when they connected to DataMap Database.

This is not the precise code you need. It's just to show you some options to help you solve your problems here. You have to have some of the "fun". 😀

 SELECT  login_name 
        ,original_login_name
        ,database_id
        ,DBName = DB_NAME(database_id)
   FROM sys.dm_exec_sessions 
  WHERE is_user_process = 1
    AND original_login_name = ORIGINAL_LOGIN()
;

I strongly suggest that you do a search for the system view I used for much more information. Also, although the speaker doesn't solve your precise problem, the following 'tube will also give you some more insight.

https://www.youtube.com/watch?v=TR2UfGALs8o

Since your messing with "god" triggers, you absolutely MUST watch the following so that you can get the system back when you eventually make a mistake.

https://www.youtube.com/watch?v=OVoS7v8tiOM

Disclaimer: I didn't know how to do any of this a half hour ago and trying to learn more because it's interesting.

Also, I've NOT checked to see if the information about the database is actually available at the time of login. There's also nothing that will prevent the user from changing databases after they login and, they usually do have to. Most people end up being taken to the "Default Database" when they first login and then have to change from there.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

homebrew01 SSC Guru Points: 55548 More actions · Answer 8

" ...There's also nothing that will prevent the user from changing databases after they login and, they usually do have to. Most people end up being taken to the "Default Database" when they first login and then have to change from there...."

I was thinking a similar thing. Not sure the usefulness of the login capture.

This reply was modified 3 years, 2 months ago by homebrew01.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 9

Have you checked to use Extended Events to capture login information without having to bother for file sizes and queue sizes?

Have a job process the captured file(s) every x minutes

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me