Post reply

Capture login information

  • 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 17, 2021 at 1:31 pm

    #3930560

    SQL Trace, or newer tool, Extended Events.

  • September 19, 2021 at 11:36 am

    #3930944

    Could also check logon trigger.

    Adi

  • September 20, 2021 at 5:08 am

    #3931041

    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)
    Intro to Tally Tables and Functions

  • September 20, 2021 at 5:18 am

    #3931042

    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)
    Intro to Tally Tables and Functions

  • September 20, 2021 at 7:03 am

    This was removed by the editor as SPAM

  • September 20, 2021 at 7:23 pm

    #3931274

    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.

     

  • September 20, 2021 at 9:11 pm

    #3931304

    How about

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

    • This reply was modified 3 years ago by  homebrew01.

  • September 20, 2021 at 11:19 pm

    #3931316

    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)
    Intro to Tally Tables and Functions

  • September 21, 2021 at 1:13 pm

    #3931463

    " ...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 ago by  homebrew01.

  • September 21, 2021 at 1:37 pm

    #3931476

    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

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

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