locked
Login modification Tracking RRS feed

  • Question

  • Hi,

         In my environment One application user had Read access in a database, but all of sudden the access is revoked. I don't know how it happened. Other than me no one has Admin access in the server.. Is there any way to track when the change happened?

    Monday, June 23, 2014 12:49 PM

Answers

  • The action may be in the default trace as suggested by Prashanth. But I don't think 'Audit Add DB User Event' is the correct event to look for. Which is the correct event, I don't know since it is not clear "access is revoked". Can the user access the database at all, that is perform "USE db", but is not able to access any tables?

    A better bet is to change the WHERE clause to

       WHERE   te.name  LIKE 'Audit%'

    This may give you a surplus of data, but you can apply further filter to remove the obvious.

    Keep in mind that the data in the default trace is lost as new rollover files are created.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Sofiya Li Tuesday, June 24, 2014 5:57 AM
    • Marked as answer by Sofiya Li Monday, June 30, 2014 9:25 AM
    Monday, June 23, 2014 8:58 PM
  • I would also like to recommend auditing operations to make easier the detection of unusual activity.

    You can create a SQL Server Audit Specification and audit any principal changes (i.e. login creation) as well as permission or group membership changes on all databases.

    The following link should be a useful reference: Create a Server Audit and Server Audit Specification

    -Raul Garcia


    This posting is provided "AS IS" with no warranties, and confers no rights.


    Tuesday, June 24, 2014 12:39 AM
  • -- Try this code....

    DECLARE @FileName VARCHAR(MAX

    SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc' 
    FROM sys.traces  
    WHERE is_default = 1

    SELECT  
        
    o.name,  
        
    o.OBJECT_ID
        
    o.create_date,
        
    gt.NTUserName
        
    gt.HostName
        
    gt.SPID
        
    gt.DatabaseName
        
    gt.TEXTData
    FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt 
    JOIN tempdb.sys.objects AS o  
        
    ON gt.ObjectID = o.OBJECT_ID 
    WHERE gt.DatabaseID = 2
     
    AND gt.EventClass = 46 -- (Object:Created Event from sys.trace_events) 
     
    AND o.create_date >= DATEADD(ms, -100, gt.StartTime)  
      AND
    o.create_date <= DATEADD(ms, 100, gt.StartTime)


    Raju Rasagounder Sr MSSQL DBA

    • Proposed as answer by Sofiya Li Tuesday, June 24, 2014 5:57 AM
    • Marked as answer by Sofiya Li Monday, June 30, 2014 9:25 AM
    Tuesday, June 24, 2014 1:30 AM

All replies

  • Can you check using below query so that will know Who has altered the Login :

    SELECT [Transaction SID],suser_sname([Transaction SID]) as 'Altered User
    Login_Name' FROM ::fn_dblog(default, default) WHERE [Transaction
    Name]='ALTER LOGIN'


    Raju Rasagounder Sr MSSQL DBA

    Monday, June 23, 2014 12:59 PM
  • You can try above code and if change was frequently done you might find it. Also please take a note that fn_dblog is unsupported command which is generally advised by Microsoft not to run on prod databases but its safe.

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles


    Monday, June 23, 2014 1:05 PM
  • Hi,

         I didn't get any result using this script.

    Monday, June 23, 2014 1:24 PM
  •     
        SELECT  te.trace_event_id,
    TE.name AS [EventName] ,
    T.EventSubClass,
    T.TargetUserName,
            T.DatabaseName ,
    
            t.DatabaseID ,
    
            t.NTDomainName ,
    
            t.ApplicationName ,
    
            t.LoginName ,
    
            t.SPID ,
    
            t.Duration ,
    
            t.StartTime ,
    
            t.EndTime
           
    
    FROM    sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1
    
                                                                  f.[value]
    
                                                          FROM    sys.fn_trace_getinfo(NULL) f
    
                                                          WHERE   f.property = 2
    
                                                        )), DEFAULT) T
    
            JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
    
    WHERE   t.StartTime > GETDATE() -1
        and te.name  =   'Audit Add DB User Event'  
       
    
    ORDER BY t.StartTime ; 
    

    Can you try the above code?

    Reference

    http://www.sqlserver-dba.com/sql-default-trace/

    --Prashanth


    Monday, June 23, 2014 2:12 PM
  • The action may be in the default trace as suggested by Prashanth. But I don't think 'Audit Add DB User Event' is the correct event to look for. Which is the correct event, I don't know since it is not clear "access is revoked". Can the user access the database at all, that is perform "USE db", but is not able to access any tables?

    A better bet is to change the WHERE clause to

       WHERE   te.name  LIKE 'Audit%'

    This may give you a surplus of data, but you can apply further filter to remove the obvious.

    Keep in mind that the data in the default trace is lost as new rollover files are created.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Sofiya Li Tuesday, June 24, 2014 5:57 AM
    • Marked as answer by Sofiya Li Monday, June 30, 2014 9:25 AM
    Monday, June 23, 2014 8:58 PM
  • I would also like to recommend auditing operations to make easier the detection of unusual activity.

    You can create a SQL Server Audit Specification and audit any principal changes (i.e. login creation) as well as permission or group membership changes on all databases.

    The following link should be a useful reference: Create a Server Audit and Server Audit Specification

    -Raul Garcia


    This posting is provided "AS IS" with no warranties, and confers no rights.


    Tuesday, June 24, 2014 12:39 AM
  • Then there was no modifications happened...

    Raju Rasagounder Sr MSSQL DBA

    Tuesday, June 24, 2014 1:22 AM
  • -- Try this code....

    DECLARE @FileName VARCHAR(MAX

    SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc' 
    FROM sys.traces  
    WHERE is_default = 1

    SELECT  
        
    o.name,  
        
    o.OBJECT_ID
        
    o.create_date,
        
    gt.NTUserName
        
    gt.HostName
        
    gt.SPID
        
    gt.DatabaseName
        
    gt.TEXTData
    FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt 
    JOIN tempdb.sys.objects AS o  
        
    ON gt.ObjectID = o.OBJECT_ID 
    WHERE gt.DatabaseID = 2
     
    AND gt.EventClass = 46 -- (Object:Created Event from sys.trace_events) 
     
    AND o.create_date >= DATEADD(ms, -100, gt.StartTime)  
      AND
    o.create_date <= DATEADD(ms, 100, gt.StartTime)


    Raju Rasagounder Sr MSSQL DBA

    • Proposed as answer by Sofiya Li Tuesday, June 24, 2014 5:57 AM
    • Marked as answer by Sofiya Li Monday, June 30, 2014 9:25 AM
    Tuesday, June 24, 2014 1:30 AM