locked
How do I audit/trace the creation or deletion of SQL user login accounts RRS feed

  • Question

  • Could you please guide me on how do I audit/trace the creation or deletion of SQL user login accounts. Many thanks in advance.
    Wednesday, June 2, 2010 12:47 PM

Answers

  • For SQL Server 2000, there are no built in capabilities for monitoring this, so you will have to build it yourself.

    For new logins, syslogins has a createdate column that says when the login was created. 

    You will need something like creating a LoginAudit table and keeping a copy of the logins in it.  Use that periodically to see if a login was added, deleted, or recreated (which means that it had been deleted).  Something like:

    CREATE TABLE LoginAudit 
    (sid varbinary(85),
     name nvarchar(128),
     createdate datetime,
     approxdeletedate datetime NULL,
     statusreported bit DEFAULT(0)); 
    
    -- Load it the first time
    INSERT INTO LoginAudit (sid, name, createdate, statusreported)
    SELECT sid, name, createdate, 1 -- starts as reported
     FROM master.dbo.syslogins
    
    -- Periodically schedule a job that does the following
    DECLARE @MaxDate DATETIME
    SELECT @MaxDate = (SELECT MAX(createdate) 
              FROM LoginAudit)
    
    -- 1. Older copies of newly created logins were deleted
    UPDATE LoginAudit
     SET approxdeletedate = GETDATE(),
       statusreported = 0
    WHERE approxdeletedate IS NULL 
     AND name in (SELECT name
            FROM master.dbo.syslogins s
            WHERE createdate > @MaxDate)
    
    -- 2. Insert new logins
    INSERT INTO LoginAudit (sid, name, createdate)
     -- Default statusreported = 0
    SELECT sid, name, createdate 
     FROM master.dbo.syslogins s
     WHERE createdate > @MaxDate 
    
    -- 3. Check for deleted logins
    UPDATE LoginAudit
     SET approxdeletedate = GETDATE(),
       statusreported = 0
    WHERE name NOT IN (SELECT name
              FROM master.dbo.syslogins)
     AND approxdeletedate IS NULL
    
    -- Report Changes to somebody
    SELECT * FROM LoginAudit
    WHERE statusreported = 0
    ORDER BY name, createdate, approxdeletedate
    
    UPDATE LoginAudit SET statusreported = 1 
    WHERE statusreported = 0
    
    RLF
    Tuesday, June 8, 2010 6:47 PM

All replies

  • You can use a DDL trigger to do this. Something similar to this should get you started:

    CREATE TRIGGER TR_DDL_AUDIT_LOGIN
    ON ALL SERVER 
    FOR CREATE_LOGIN, DROP_LOGIN
    AS 
    	SELECT EVENTDATA().value('(//EventType)[1]', 'nvarchar(max)')
    	SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)')
    GO

    every day is a school day
    Wednesday, June 2, 2010 1:06 PM
  • ... Or use Event Notification. Or, if on 2008 EE or higher, use "Server Audit" functionality.
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Thursday, June 3, 2010 5:24 PM
  • Two more options

    1. SQL Server 2008 built-in auditing, SERVER_PRINCIPAL_CHANGE_GROUP (http://technet.microsoft.com/en-us/library/cc280663.aspx) or use a more granular server audit specification.

    2. SQL Server Profiler event - "Audit Server Principal Management Event" (the way to do C2 or common criteria auditing before SQL Server 2008 Auditing). (http://technet.microsoft.com/en-us/library/ms188715(SQL.105).aspx)

    In the default trace it gets audited. Below is the sample

    Event class

    Audit addlogin event

    NT username

    xxxx

    ......... you will have all other details like spid , time , login name etc

    and finally you will see

    Target loginname

    Test ( newly created login)


    Thanks, Leks
    Thursday, June 3, 2010 8:53 PM
  • Many thanks for the replies from you all. However, the server is running SQL server 2000.

    Could you please provide me the guides on how can I achieve this audit checking for creation or deletion of SQL user account.

    Thank you so much in advance.

    Friday, June 4, 2010 1:25 PM
  • For SQL Server 2000, there are no built in capabilities for monitoring this, so you will have to build it yourself.

    For new logins, syslogins has a createdate column that says when the login was created. 

    You will need something like creating a LoginAudit table and keeping a copy of the logins in it.  Use that periodically to see if a login was added, deleted, or recreated (which means that it had been deleted).  Something like:

    CREATE TABLE LoginAudit 
    (sid varbinary(85),
     name nvarchar(128),
     createdate datetime,
     approxdeletedate datetime NULL,
     statusreported bit DEFAULT(0)); 
    
    -- Load it the first time
    INSERT INTO LoginAudit (sid, name, createdate, statusreported)
    SELECT sid, name, createdate, 1 -- starts as reported
     FROM master.dbo.syslogins
    
    -- Periodically schedule a job that does the following
    DECLARE @MaxDate DATETIME
    SELECT @MaxDate = (SELECT MAX(createdate) 
              FROM LoginAudit)
    
    -- 1. Older copies of newly created logins were deleted
    UPDATE LoginAudit
     SET approxdeletedate = GETDATE(),
       statusreported = 0
    WHERE approxdeletedate IS NULL 
     AND name in (SELECT name
            FROM master.dbo.syslogins s
            WHERE createdate > @MaxDate)
    
    -- 2. Insert new logins
    INSERT INTO LoginAudit (sid, name, createdate)
     -- Default statusreported = 0
    SELECT sid, name, createdate 
     FROM master.dbo.syslogins s
     WHERE createdate > @MaxDate 
    
    -- 3. Check for deleted logins
    UPDATE LoginAudit
     SET approxdeletedate = GETDATE(),
       statusreported = 0
    WHERE name NOT IN (SELECT name
              FROM master.dbo.syslogins)
     AND approxdeletedate IS NULL
    
    -- Report Changes to somebody
    SELECT * FROM LoginAudit
    WHERE statusreported = 0
    ORDER BY name, createdate, approxdeletedate
    
    UPDATE LoginAudit SET statusreported = 1 
    WHERE statusreported = 0
    
    RLF
    Tuesday, June 8, 2010 6:47 PM