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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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.
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.
You can enhance the logic to check for database name, my purpose is not to give you a copy-paste working code.