I don’t know if you’re a fan of NCIS, but for those of you who aren’t let me give you a little back story. Gibbs is the leader of an investigative team and he has a set of rules he teaches his team members in order to help make them better at their jobs. He doesn’t teach them all at once. And he never writes them down. But nearly everyone around him becomes better, because they learn the rules.
As a DBA, I have rules too. Today, I’m feeling I should share one with you, it’s very similar to Gibbs’ Rule #8: “Never take anything for granted.”
Rule #1: Developers are not granted sa privileges.
No developer has the need for sa or dbo rights. None of the developers that I’ve worked with were great Database Administrators. Very few were good Database Programmers. I don’t expect them to be. I expect them to be good programmers. I do not expect my developers to build database objects and implement them on the servers.
Developing database objects require a specific style of reasoning. Applying atomic, object based, programming to SQL can often lead to poor performance. Developers can propose new objects, and even draft them, but you need a Database Engineer or Database Administrator to check out the design, and ultimately implement those objects on the server. But lacking this specialized knowledge is only one reason to not allow developers sa privileges.
As sa, you can do anything on a server. Do you really want to leave your company vulnerable to a disgruntled employee? Even worse, what if that programmer’s machine were to be lost. Now a complete stranger has access to your server as sa. You may think your programmers don’t write down their passwords, or save them in their connection strings. Don’t delude yourself. If everyone uses their own username and password, you can lock out one user, rather than all users. And your sa account, and therefore your SQL server is closer to safe!
How can you tell who did what in the logs, when everyone is sa? You are logging everything aren’t you? You are logging everything, right? Hrm… That’s another rule. if everyone logs in as sa, even worse, your applications log in as sa, how can you tell who made what change when it comes time to answer questions in an audit. If everyone has separate accounts, you can tell immediately who did what!
If developers have sa privileges, they can make changes to the database or server’s settings. Then you run the risk your development environment can become different than your production environment. Then debugging can become increasingly more difficult. You are running a separate development environment, right? Sounds like another rule.
I’m sure there are more reasons why developers are not granted sa privileges. Feel free to share more reasons you agree with me. If you disagree, you’re going to have to have a supremely good reason to convince me, but I look forward to the challenge. If you have any questions about my rules, or suggestions for new rules, send them in! I look forward to learning from you, as much as I look forward to sharing with you!