current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 4 down vote favorite

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?

share|improve this question
8  
Give people only the permissions they require instead of relying on failsafes. – Phil yesterday
    
I know this is the easy way to do that... Unfortunately in our organization everyone use the same user... – ufo yesterday
    
That is really a bad practice. – Shanky yesterday
    
I know... But I'm not able to change this... – ufo yesterday
3  
Let me guess, the user's name is sa :-) – dnoeth yesterday
up vote 2 down vote accepted

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.

share|improve this answer
    
I think is the best solution for us... – ufo yesterday
up vote 9 down vote

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.

share|improve this answer
    
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 yesterday
    
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. – John M yesterday

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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