4

I've got 2 databases which are very important for our organization. I would like to prevent an accidental drop/delete of them... Do you have any suggestion? I was thinking about asking a password before perform the drop. Is this possible in MS SQL Server?

5
  • 8
    Give people only the permissions they require instead of relying on failsafes.
    – Philᵀᴹ
    Mar 8, 2016 at 14:24
  • I know this is the easy way to do that... Unfortunately in our organization everyone use the same user...
    – ufo
    Mar 8, 2016 at 14:48
  • That is really a bad practice.
    – Shanky
    Mar 8, 2016 at 14:57
  • I know... But I'm not able to change this...
    – ufo
    Mar 8, 2016 at 15:02
  • 3
    Let me guess, the user's name is sa :-)
    – dnoeth
    Mar 8, 2016 at 15:28

2 Answers 2

3

You are operating in a very risky security environment by sharing a single user with all permissions. The quickest solution is to revoke DB_Owner on the shared account for each database but leave reader/writer/ddladmin/backupoperator/accessadmin. That will prevent the shared User from dropping any databases they are attached to but should still have control over all the innards of those particular databases. If you need a User aside from your System Admin accounts that can drop databases you can create it separately and only use it when necessary.

1
  • I think is the best solution for us...
    – ufo
    Mar 8, 2016 at 16:00
10

You should, as the comment says, rely on permissions for this.

Strictly speaking, is not possible to "ask password". Where would the password dialog even be displayed?? SQL Server is a client server product that accept commands from any client that uses the TDS protocol. That may be Management Studio, sqlcmd.exe, a PowerShell script, a ADO.Net SqlClient application. Anything.

If you must, then you can deploy, as a last resort, a DDL trigger. A DDL trigger will not replace permissions, but it can prevent an accidental drop.

CREATE TRIGGER safety 
ON SERVER
FOR DROP_DATABASE 
AS 
   PRINT 'You must disable Trigger "safety" to drop databases!' 
   ROLLBACK;

You can enhance the logic to check for database name, my purpose is not to give you a copy-paste working code.

2
  • Yes, you are right! I meant in Management Studio... Anyway permissions would be the answer, but everyone in our company use the same user...
    – ufo
    Mar 8, 2016 at 14:50
  • And don't forget to periodically check to make sure the trigger gets re-enabled once they do the drop! I know I'm guilty of disabling some of my triggers when working and not re-enabling them afterwards. Mar 8, 2016 at 15:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.