I was asked a rather interesting question during an interview recently. It went something like this:
Your resume says you’ve implemented security standards. What do you do if a development team comes to you and says “We have to have a SQL Server Id in production (or other restricted permission) because that’s the way we tested it and we are going into production now.”?
And in general my response is “You give it to them. It’s too late to stop them at that point.”
If you are waiting until the move to production to restrict permissions you are waiting far too late. This needs to start in dev. As an example we had a team who the service account for their new application to the db_ddladmin role. No big deal in dev right? We didn’t even catch it in test. They get to model and all of a sudden their truncates no longer work. Truncate requires the alter permission on the table. That’s included with db_ddladmin but is a somewhat unusual permission to grant someone outside of it. I mean you don’t normally want your application able to add or remove columns right? Fortunately that was a relatively easy one to fix. The one where they had the application creating and dropping tables all over the place (for some reason they didn’t want to use temp tables) was a little more complicated.
It comes down to this. If you want your application to exist with a given set of security standards when you get to production, then you need to make sure it’s written and tested with those permissions. I.e. your dev and test environments. If you were wondering, this is why, among other reasons, that I don’t give sysadmin, or db_owner even in a development environment.