I've got a payroll-related schema that I need to secure. I figure if I prevent all users from directly modifying the tables, and create a set of procedures to handle all data modifications, we should be in good shape. I can have the procedures handle all the audit logging, wrap things in transactions as needed, etc.
But to do this, I need to deny direct DML statements to everybody on this particular schema - even the sysadmin server role. Is there any simple way to do this without resorting to INSTEAD OF triggers (which wouldn't be quite as bulletproof)? I don't see any syntax like "DENY INSERT, UPDATE, DELETE ON SCHEMA::schemaname TO ALL", and denying permissions to the "public" role doesn't seem to have the desired effect.