MSBI Tutor

SELECT        name AS 'LoginName'        , LOGINPROPERTY ( name , 'BadPasswordCount' ) AS 'BadPasswordCount'        , LOGINPROPERTY ( name , 'BadPasswordTime' ) AS 'BadPasswordTime'        , LOGINPROPERTY ( name , 'DaysUntilExpiration' ) AS 'DaysUntilExpiration'        , LOGINPROPERTY ( name , 'DefaultDatabase' ) AS 'DefaultDatabase'        , LOGINPROPERTY ( name , 'DefaultLanguage' ) AS 'DefaultLanguage'        , LOGINPROPERTY ( name , 'HistoryLength' ) AS 'HistoryLength'        , LOGINPROPERTY ( name , 'IsExpired' ) AS 'IsExpired'        , LOGINPROPERTY ( name , 'IsLocked' ) AS 'IsLocked'        , LOGINPROPERTY ( name , 'IsMustChange' ) AS '...

Step 1: Go to Audits under security tab Step 2: Right click on audit and create audit Using SQL script CREATE SERVER AUDIT   SQL_PWD_Audit     TO FILE ( FILEPATH = 'D:\LogFolder\' )         WITH ( QUEUE_DELAY = 1000 , ON_FAILURE = CONTINUE ); GO Step 3: Create  server audit specification  Using SQL script CREATE SERVER AUDIT SPECIFICATION  Srv_Audit_PWD FOR SERVER AUDIT   SQL_PWD_Audit     ADD ( LOGIN_CHANGE_PASSWORD_GROUP ); GO Step 4: Enable Audit and  server audit specification  Step 5: Try to change password any one user Step 6: By using bellow query we can check audit logs SELECT * FROM sys . fn_get_audit_file ( 'D:\LogFolder\*' , default , default ) WHERE action_id = 'PWR' GO Another way to check audit logs