GilaMonster (9/30/2014)
Eric M Russell (9/30/2014)
So, if a DBA removes an end user from the DBO role in the process of performing a migration to the next release of SQL Server, and then he says the following (not to management or a client... but to the end user for whom he revoked DBO membership), you guys would consider it a lie, an outright deceit?"I'm so sorry, I know it a pain in the a$$, but under SQL Server 20xx a login can't drop/recreate objects unless we make them database owner or sysadmin. It's a new default feature intended to tighten security and prevent SQL injection attacks. Do we really want to operate under a non-standard security configuration? It's best just to leave it as is and let the DBA execute all the deployments."
Yes, it's an outright attempt to deceive.
1) There's no such new default feature
2) A login most certainly can drop and recreate objects without being database owner or sysadmin.
If you're going to remove DBO rights, be upfront about why.
"Management has mandated that no member of the development team may have dbo on a production database"
"It's bad practice to have developers changing code in production without the knowledge of the DBA team, hence from now on the policy is that only DBAs have sysadmin"
"The auditors have said that unless we remove the DBO rights from the development team, we're in violation of the audit requirements and are subject to penalties of ..."
If the decision to tighten security comes from upper management or is part of an effort to comply with auditors, then those details would be part of a broader conversation with users, most probably delivered by management.
My earlier comments would be in response to a developer who asks "Why can't we just deploy our own procedures; why is it only your domain as the DBA?". While it is technically possible to grant them alter/drop/create permission, without granting them membership in DBO or admin role, it would still present other security issues, like unsafe code modifications (ex: SQL Injection), for which the DBA would not be made aware of. I'd prefer not to even acknowledge that as an option, and instead suffice it to say that the developer writes the code and deploys to development and the DBA deploys to production.
I'm sorry for steering this conversation off-topic; I was just curious about why some felt it was phrased in a way that made it dishonest. In the end, I agree with your assessment of it, Gail. I do believe it would be better to take a more straightforward approach in explaining to developers why it's a bad idea for anyone other than the DBA to deploy to production.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho