Sean Lange (7/25/2013)
Sounds like you guys have kind of painted yourselves into a corner. There is a way to do this but it is not exactly best practice. Basically what you are trying to do is make a table readonly unless the current is xxx.You could create instead of triggers on this one table and check SUSER_NAME(). If it is not the user that can modify the table you could either raise an error or simply exit.
Yeah, recently someone have a presentation at our local mssql user group meeting on how he uses logon triggers, etc. in an attempt to block accounts with sysadmin membershop from harming his production database. It seems to be a daily struggle, and he obviously put a lot of thought and effort into it. The sad thing about it is that one of these rogue sysadmins can simply drop the trigger, if they were intent on doing some damage.
Really, if the DBA would just drop the accounts from sysadmin role, and add them as db_datareader / db_datawriter, that would cover anything that an application account would need to do. It's not as if a user will call up help desk and complain because they can't change the MAXDOP setting or truncate the transaction log. Even if they did, then that's obviously something that the DBA would want routed through his department anyhow.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho