This is a long posting, please read it through and then give me your thoughts on the subject or your experiences.
I have a SQL Hotel, I e lots of databases 'rent' room on my clustered hotel. Whenever a new project needs a database it's up to me to make that happend.
If the database is created by some install program and user is hardcoded as SA, then I run against a temporary SQL server, then I move the database, users, jobs and endpoints, if they exist to the SQL Hotel.
But if the program allows me to choose user myself, then I would like to let it install against my cluster live (no I dont have a sufficient test environment, getting there slowly). If I grant a account (domain and SQL) the dbCreator role, it can drop ANY
database. This is in my opinion not acceptable. Im thinking about creating a trigger like:
DDL Trigger, unfortuantly fires after drop database command, but something like this...
If user is member of sysadmin, then Return (exit trigger do nothing),
else rollback, check DBO of the database, if user is DBO, disable the trigger and drop the database again, otherwise let the drop database be rolledback and log the event to eventviewer.
Two drawbacks, I cannot suppress the message that it's not deleted (need a rollback to check weather DBO in the database is the account issuing the drop), so I will have to construct two messages, both explaining that the first
View Complete Post