SQLServerCentral Article

Keeping Your Developers Under Control

,

Keeping Your Developers Under Control

Introduction

I love my children. They bring some of the greatest joy into my life as I watch

them learn, develop new skills, and break the rules my wife and I set for them. Yes,

breaking rules (one of kids favorite pasttimes) is often frustrating and annoying at the

time, but funny later. Much to their annoyance as well, we have to stop them and enforce

rules to be sure that they learn to respect them and some semblance of order is maintained

in our household.

Not that I dislike developers, or think they are like my kids, or even that I even think they are out of control.

It's just that anyone will wreck havoc on a stable database environment (or any other one for that matter) if

there are not rules they have to follow. And not only are the rules needed, but they need to be

enforced. There are a number of tools available within SQL Server to setup and enforce rules

which can help ensure a stable database environment. This article will discuss the security

setup that I use to ensure that I can maintain order in my company with regard to the

SQL Servers. The second part to this series will focus on techniques to enforce rules that

are setup in any environment.

What Are The Rules?

Over the years, working by myself and with all sizes of teams from two to thirty, I have

decided there are some rules that are needed to ensure that development can proceed efficiently

and effectively in a database environment. Here are a few that I follow to try and prevent problems

from occuring.

  • "sa" and "dbo" rights are limited to DBAs and Change Control Personnel
  • Developers have no rights on production servers

  • All objects are owned by "dbo"
  • Everything is logged (see Log Your Changes)
  • Everything is scripted
  • Everything is under version control

These are simple and effective, though sometimes annoying rules. I will discuss each briefly, though

over time I will update and expand this article (or devote an article to each) to explain each of these

in more detail with reasoning, give justification, and techniques for enforcement.

Rights

Everyone wants rights. This country was founded because our forefathers wanted more rights

than they had. Well, in my SQL environment, it's a dictatorship. No developer has the need for

"sa" or "dbo" rights. There is nothing involved in development that requires these rights. Well,

let me qualify this slightly. Unless the developer is specifically working on administrative tools, then

they may require additional rights to test their code, but in all the development projects I have

worked on within a non-system database, they do not need "dbo" or "sa" rights.

Why not? Because this is the easiest way to maintain control over the changes that occur on a development

server. If developers can change db settings or server settings, there are two possible

side effects. One, the development server becomes different than the production server and

the changes may not be compatible with the production server. This is a waste of development

time and none of us needs delivery dates to slip more than they already do.

Two, the DBA needs to

be aware of changes that are made so they can be migrated to the production server easily with a

minimum of downtime. How many times has any of you moved code from a test to a live environment and

something did not work? Then "Oh yeah, I forgot about that (DLL, procedure, setting, etc.)?"

was uttered by the developer working on the change? It has happened to me more times than I

care to admit (even caused by me more times than I care to remember). It has happened much, much

less since I started to lock down rights on all SQL Servers, development and production.

A couple jobs ago, I walked in the first day and found that all developers (6), and the junior

DBA (1) had sa rights on three development, two QA, and two production SQL Servers. Development proceeded

on the development boxes and then each developer moved his or her changes to the QA box. When

something did not work as expected, the developer made the change on QA. Then the DBA moved the

changes to production and when bugs were found, the developer or DBA made the changes on the production

server. Care to guess how much downtime was experienced? More than management wanted, and it consistantly

occurred during every release of new software for days afterward. You can imagine why the

previous DBA left and how I got a job. Not a fun situation to start work in.

How did I correct this? First thing, change the "sa" password and keep it to

myself and the other DBA. On ALL servers. Next, create a "development" group (role) in

the development databases and move all developers into this group. They were granted

rights to "create procedures" and "create views" only. In some cases I have granted

"create table" rights, but rarely. I prefer to discuss schema changes with the developer

prior to implementation to ensure things are normalized, not duplicated, etc. This did

two things. One, it controls which changes can be made without the DBA's knowledger while

allowing developers to get work done. Two, it ensures the DBA is informed of changes prior to

implementation. Objects created by the developers cannot be executed by anyone other than the

owner, so they have to deliver these to the DBA for recompilation as "dbo" before they can be

tested (you do have someone else test the code, right?).

Production Server Rights

None. Simple and leave it at that. Developers should not be making any changes to

a production environment without testing. Since no one should deliver code that is not

tested by someone else, let the developer develope on a test server, give the code to a DBA

or someone responsible for managing change, and let that person make the change on

production.

Who Owns What?

On the production servers, keep it simple. Let all objects be owned by the dbo,

set appropriate permissions for groups (roles) and you will minimize the security

problems that occur. If different people own different objects, then migrations, maintenance,

and control quickly spiral out of control. Do yourself a favor and let dbo own everything.

Everything Is Logged

All changes have to be logged in the production environment as a minumum. See Log

Your Changes

Everything Is Scripted

This one deserves a long article to itself, but here are the highlights. The GUI is nice

and makes changes easy. However, it probably causes more DBAs to utter the dreaded

"Oh yeah, I forgot about that (DLL, procedure, setting, etc.)?" than anything else. We are

all human, and with your log (you DO have a log, right?) you can recreate these changes, but

at a minimum, you have to do everything twice (once to develop and once to migrate). It

takes only slightly longer to script things, and there are some features in the SQL 2000

tools to make this easier. If you have a script, then duplicating your work on the

production (or ten production) server(s) is easier.

Everything Is Under Version Control

Everyone has a version control system (VCS) (right?, please?). Do you let your kids play with

matches? Without a version control system, that's what developers are doing. Of course, with a

VCS and not using it, the same thing happens, but that's a different issue. Like the scripting rule,

this one deserves a longer treatment, but here are some quickies.

One, while rare you will "roll back" to a previous version, just like you "rarely" restore your

database from a backup, a version control system is just good sound development practice. It also

provides that same backup system that pressing "Save" in Word often does. It saves you when

your machine crashes (though in four months of Win2000 Professional, I have zero crashes).

In many of today's larger, distributed shops, this also provides a central point of code location

that ensures that different members of a team do not "step on" or overwrite another's hard work. This

is perhaps one of the easiest methods of keeping some sort of order in your development shop. If you do not

have a "VCS" system, you can always make a series of folders on a network drive and copy code there

every night, but this does not keep developers from overwriting someone else's changes. You need some

strong administrative policies if you choose this route.

Conclusion

I apologize for the short treatment of some of these topics, but, well, this thing is

long enough. I will spend some time on each of these areas with more examples and reasoning

in other articles and eventually this should be a good guideline for setting up an environment.

Perhaps even a good set of white papers you can use to justify yourself to management.

As always, I welcome feedback, comments, and suggestions. This is a topic that is often

asked about and discussed and if anyone has better ideas or disagrees, please let me know with an

email.


Return to Steve Jones Home

 

Rate

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (1)

You rated this post out of 5. Change rating