locked
Tracking logs for new Login and new user. RRS feed

  • Question

  • Hi,

    Is it possible to get the logs when someone creates a new user and login from SQL Audit feature?

    SQL version: SQL Server 2014


    aa

    Wednesday, April 6, 2016 7:28 AM

Answers

  • Please note that SERVER_PRINCIPAL_CHANGE_GROUP covers these actions:

    1. When server principals are created, altered, or dropped. 
    2. When a principal issues the sp_defaultdb or sp_defaultlanguage stored procedures or ALTER LOGIN statements.
    3. Event is raised on the sp_addlogin and sp_droplogin stored procedures. Also equivalent to the Audit Login Change Property Event Class.
    4.Event is raised for the sp_grantlogin, sp_revokelogin, or sp_denylogin stored procedures. 

    For more information, refer this link:

    https://technet.microsoft.com/en-us/library/cc280663%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396




    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    • Marked as answer by Asif_DBA Wednesday, April 6, 2016 8:05 AM
    Wednesday, April 6, 2016 8:02 AM

All replies

  • Sure why not: You need to first create Server Audit and then define Server Audit Specification containing relevant Action types, such as:

    CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20160406-131613]
    FOR SERVER AUDIT [Audit-20160406-130907]
    ADD (SERVER_PRINCIPAL_CHANGE_GROUP)
    
    GO
    
    CREATE SERVER AUDIT SPECIFICATION [Audit Login]
    FOR SERVER AUDIT [Audit Login Changes]
    ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
    ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
    ADD (DATABASE_PERMISSION_CHANGE_GROUP),
    ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),
    ADD (SERVER_PERMISSION_CHANGE_GROUP),
    ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
    ADD (SERVER_PRINCIPAL_CHANGE_GROUP)
    WITH (STATE = ON)
    
    In the above Server_Principal_Change_Group should cover new user provisioning


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Wednesday, April 6, 2016 7:49 AM
  • ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP), ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP), ADD (DATABASE_PERMISSION_CHANGE_GROUP), ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP), ADD (SERVER_PERMISSION_CHANGE_GROUP), ADD (DATABASE_PRINCIPAL_CHANGE_GROUP), ADD (SERVER_PRINCIPAL_CHANGE_GROUP)

    Which one is specifically for new logins and new user?


    aa

    Wednesday, April 6, 2016 7:52 AM
  • Please note that SERVER_PRINCIPAL_CHANGE_GROUP covers these actions:

    1. When server principals are created, altered, or dropped. 
    2. When a principal issues the sp_defaultdb or sp_defaultlanguage stored procedures or ALTER LOGIN statements.
    3. Event is raised on the sp_addlogin and sp_droplogin stored procedures. Also equivalent to the Audit Login Change Property Event Class.
    4.Event is raised for the sp_grantlogin, sp_revokelogin, or sp_denylogin stored procedures. 

    For more information, refer this link:

    https://technet.microsoft.com/en-us/library/cc280663%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396




    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    • Marked as answer by Asif_DBA Wednesday, April 6, 2016 8:05 AM
    Wednesday, April 6, 2016 8:02 AM