Hot answers tagged security
Greenstone Walker is right. If you are granted SA rights, there really isn't much to do there to prevent it. I like that you are worrying about protecting from your ability to make such a mistake. I've seen people burned by an "oops" restore before. Not pretty. There are potentially a few things you can do, though. Your milage may vary but some ideas to get ...
First, you can't prevent a sysadmin from doing anything. :-) Any login that is a member of the sysadmin fixed server role operates outside the permissions system - they can do anything. My first thought was to use a DDL trigger to stop the restore. Unfortunately, restoring a database is an operation that does not cause any triggers to fire (see this Connect ...
The solution is to add the Grant permissions and do not add any read or write role to the user. GRANT SELECT ON SCHEMA::[SCHEMA] TO [USER] GRANT INSERT ON SCHEMA::[SCHEMA] TO [USER] GRANT UPDATE ON SCHEMA::[SCHEMA] TO [USER] GRANT DELETE ON SCHEMA::[SCHEMA] TO [USER]
Only top voted, non community-wiki answers of a minimum length are eligible