locked
Is it possible to prevent DB Deletion from SSMS? RRS feed

  • Question

  • I have a delete lock at the Resource Group level where I have my Azure SQL DB - but I can still delete the db with no warning/prompt from SSMS.

    Is there something else I need in place to change this behavior?

    Wednesday, January 3, 2018 3:55 AM

All replies

  • Hello,

    Let me share this thread with more people and try to get a feedback for you.



    Thank you for your patience.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    Wednesday, January 3, 2018 11:03 AM
  • Hello,

    Could you please confirm you followed the steps provided on below article?

    https://docs.microsoft.com/en-us/azure/azure-resource-manager/resource-group-lock-resources#who-can-create-or-delete-locks-in-your-organization



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    Wednesday, January 3, 2018 3:14 PM
  • Yes - I followed the steps that describe setting the lock in the Portal
    Wednesday, January 3, 2018 4:11 PM
  • Hello,

    I am trying to get some help. Thank you for your patience.



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    Wednesday, January 3, 2018 8:18 PM
  • Hello,

    I have not been able to find help or receive any feedback about this issue yet.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    Thursday, January 4, 2018 1:50 PM
  • I'm seeing the same behavior - i.e. I can drop a database despite the lock being applied on the resource group level and inherited by the server/db (as indicated by the Azure portal). In addition, I don't see any entries in the Activity Log that would indicate that the database has been dropped (as https://blogs.msdn.microsoft.com/wayneb/2016/08/12/how-to-tell-who-drop-your-azure-sql-database/ suggests)

    thnx
    Marcin

    Thursday, January 4, 2018 2:53 PM
  • Hello Marcin,

    Thank you for confirming this issue.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    Thursday, January 4, 2018 3:46 PM
  • Our apologies for the issue. The engineering team will push a fix as soon as possible. Best regards.
    Friday, January 19, 2018 1:33 AM
  • Good day all,

    As I see it, the lock in the database level is in fact working as expected. Well.... if you get my explanation then it is the expected behavior :-) 

    I wrote a very short blog in first draft only to be able to post it here, but I do want to add a lot more information since this behavior is not limited to the Azure locks but to other elements like "Activity log" and "Activity log alert". These all element will not react to the queries but only to operations sent to https://management.azure.com like using the Azure Portal :-)

    Here is the first draft:
    http://ariely.info/Blog/tabid/83/EntryId/194/Azure-Lock-resources-Does-is-work-as-expected-for-Databases.aspx


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    • Edited by pituachMVP Friday, January 19, 2018 4:50 AM
    Friday, January 19, 2018 4:45 AM
  • Oh... you can skip 90% of the beginning of the post where I simply invite you to Israel (to the SQLSATURDAY) :-)

    The actual content is at the second part which is short (at this time, since now I am going to elaborate and speak about the other elements and how it is working regarding the Azure traces in general behind the scene)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Friday, January 19, 2018 4:48 AM
  • I have a delete lock at the Resource Group level where I have my Azure SQL DB - but I can still delete the db with no warning/prompt from SSMS.

    Is there something else I need in place to change this behavior?

    Good day,

    As I explained in my short blog post the Azure Lock is not expected to solve your needs (at least not in the way it is working now).

    I think that your best option is simply to work like a DBA :-)

    SSMS is simply a client application and we should treat as such. Like any client application it connect to he server using simple connection string and a LOGIN. The solution should be the same as a general question of "how prevent DB Deletion from client application using queries", and the answer is according

    You should not let people connect (LOGIN) your database using administrator privileges. Create a new LOGIN and a new USER and give the user only the permissions to use the element inside the database but not to be owner of the database.

    -- Open connection to the master database
    -- Create new LOGIN
    CREATE LOGIN AppLogin 
    	WITH PASSWORD = 'Use@Random#Complex$Pass-NotThis!' 
    GO
    -- Open connection to the specific database -- add new USER CREATE USER AppUser FOR LOGIN AppLogin WITH DEFAULT_SCHEMA = dbo GO

    -- grant the user any permission that he need
    -- DO NOT MAKE HIM OWNER OF THE DATABASE


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    • Edited by pituachMVP Friday, January 19, 2018 9:30 AM
    Friday, January 19, 2018 6:40 AM