tracking and tracing connections from SQL DB Administrators RRS feed

  • Question

  • tracking and tracing connections from SQL DB Administrators

    I have a SQL2008R2 STANDARD

    SQL DB administratoes are claiming that they are connecting every day to do managemente and maintenance tasks (and charging an ammount of monay based on this work)

    So, i would like to have a log or audit of information with the dates/times and user names used on these connections to create a corelation, comparing what is being charged and comparing to evidences of the log/audit

    So i´ve tried two approaches:

    1) changing the OS audit configuration, including logon, kerberos, etc

    2) create some Log/audit information

    I asked the DB admin to connect using MGMT studio and he did

    1) no info on windows security log regarding the connection, not even IP addres of the source machine, kerberos authentication

    2) i couldn´t create the audit to security log because i´m using STD edition

    what should i do?

    Wednesday, May 24, 2017 2:40 PM

All replies

  • You could enable 'both failed and successful logins' under Login auditing in Security page of Server Properties:

    Restart of SQL Server service is required in order for change to take effect.

    Then, disable any SQL Logins and make sure that DBA's are logging in with their Windows domain account (Windows authentication) so that you can track exactly who logged in.

    Just because someone is logging in, doesn't mean they are actually doing anything. Consider having the DBA's provide you a written report daily or weekly that describes exactly what they are doing and how long each task took. This should both educate you on what they are doing as well as help ensure that they are charging time accurately, according to what is actually being done.


    Phil Streiff, MCDBA, MCITP, MCSA

    Wednesday, May 24, 2017 5:28 PM
  • but all DB admins are using SQL (internal) logins

    and restart SQL services are not na option for a while...

    but ok, good answer for now

    i heard about profiler and login triggers..

    Wednesday, May 24, 2017 6:53 PM