locked
Login Password RRS feed

  • Question

  • Hello - is there a way to tell who or when a login password got changed in sql server 2012? We do not have any auditing setup. What ways can we audit login password changes?

    Thanks!!!

    Wednesday, July 1, 2015 4:21 PM

Answers

All replies

  • This will work for the properties but not for the password changes. Also the default trace is a rolling window so it might be short lived on a busy server.

    DECLARE @dftrc nvarchar(520), 
    	@filename nvarchar(520), 
    	@OldestFile nvarchar(520)
    
    SELECT @dftrc = path, 
    	@filename = REVERSE(SUBSTRING(reverse(path),0,CHARINDEX('\',reverse(path)))),
    	@OldestFile = REPLACE(path, REVERSE(SUBSTRING(reverse(path),0,CHARINDEX('\',reverse(path)))), '') + (N'log_' + convert(nvarchar(520), (CONVERT(INT, REPLACE(REPLACE(REVERSE(SUBSTRING(reverse(path),0,CHARINDEX('\',reverse(path)))), '.trc', ''), 'log_', '')) - (max_files-1))) + N'.trc')
    FROM sys.traces 
    WHERE is_default = 1
    
    select name, a.*
    from ::fn_trace_gettable(@OldestFile, default) a
    join sys.trace_events b on (a.eventclass = b.trace_event_id)
    where name = 'Audit Login Change Property Event'

    The best way to track this stuff is with an audit.

    See the following for all your options:

    http://www.mssqltips.com/sqlservertip/2708/tracking-login-password-changes-in-sql-server

    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

    Wednesday, July 1, 2015 5:03 PM
  • You can get the last password change timestamp from LOGINPROPERTY (Transact-SQL) => PasswordLastSetTime ; see also When will a SQL login password expire?

    Only the user himself and a DBA/security admin can change the password, so the question of who changes result in a small set of persons.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    • Edited by Olaf HelperMVP Wednesday, July 1, 2015 5:17 PM
    • Marked as answer by mkl69 Wednesday, July 1, 2015 8:26 PM
    Wednesday, July 1, 2015 5:16 PM
  • You can get the last password change timestamp from LOGINPROPERTY (Transact-SQL) => PasswordLastSetTime ; see also When will a SQL login password expire?

    Only the user himself and a DBA/security admin can change the password, so the question of who changes result in a small set of persons.


    Olaf Helper


    Nice!

    Here's a query to use the login property for all SQL logins:

    SELECT name, LOGINPROPERTY(name, 'PasswordLastSetTime') 
    FROM sys.sql_logins

    Emanlee00, Keep in mind that all of these examples don't count for any NT (Windows) based credentials.

    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)



    Wednesday, July 1, 2015 5:51 PM
  • You can get the last password change timestamp from LOGINPROPERTY (Transact-SQL) => PasswordLastSetTime ; see also When will a SQL login password expire?

    Only the user himself and a DBA/security admin can change the password, so the question of who changes result in a small set of persons.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]



    is there a way to find out which user change the password?
    Wednesday, July 1, 2015 6:37 PM
  • You'd have to do that with the directions I detailed in my first post.
    Wednesday, July 1, 2015 7:01 PM