locked
How to find when a user in a database is dropped, not the login though RRS feed

  • Question

  • How to record when a user in a database is dropped for Audit purposes. Not concerned abt the corresponding  LOGIN

    Thanks


    kris vemulaalli

    Wednesday, October 4, 2017 8:02 PM

All replies

  • The techniques that comes into mind are DDL triggers and event notification. It does not seem that this can be tracked through SQL Audit, although I am not very good at that feature.

    Wednesday, October 4, 2017 9:31 PM
  • The techniques that comes into mind are DDL triggers and event notification. It does not seem that this can be tracked through SQL Audit, although I am not very good at that feature.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, October 4, 2017 9:32 PM
  • Hi Kris,

    >> How to record when a user in a database is dropped for Audit purposes. Not concerned abt the corresponding  LOGIN

    Basically you can achieve your requirement by creating DDL trigger, see code example below:

    CREATE TRIGGER dropDatabaseUser

    ON DATABASE  

    FOR DROP_USER

    AS

    BEGIN

                      SELECT CONVERT(varchar(23), getDate(), 121) AS dropDatabaseUserTime

    END

    Then run the following query to see when a user in a database is dropped.

    USE [databaseName]

    GO

    IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'userName')

    DROP USER [userName]  --It will drop the user from the database,   without dropping the login from the SQL Server instance.

    GO

    If you have any other questions, please let me know.

    Regards,

    Hannah


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Hannah Yu Wednesday, October 18, 2017 4:40 AM
    Thursday, October 5, 2017 10:37 PM
  • Hi Kris,

    If you are having default traces on the machine, you can figure out or from default schema change history from reports.

    https://www.mytechmantra.com/LearnSQLServer/SQL-Server-Find-which-user-deleted-the-database-in-SQL-Server/


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

    Sunday, October 8, 2017 6:16 PM
  • Hi Kris,

    Any update? 

    If you have any other questions, please let me know.

    Regards,

    Hannah 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 16, 2017 9:45 AM
  • Hi Kris,

    Have you solved your problem yet? Did my reply help? If so, would you please mark it as answer so that people from other forum can benefit from it?

    If you have any other questions, please let me know.

    Regards,

    Hannah


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 18, 2017 4:32 AM