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 ..."
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability