We have recently had an internal discussion on service accounts predominantly on the subject of whether it is better to have one service account for sql server that is used in all environments or whether separate accounts should be used in each environment. Perhaps surprisingly everyone came down on the same side (how often does that happen???) and we all seemed to agree on every point. I thought I'd share this with you.
We agreed that the advantages of having separate accounts clearly outweigh the overheads in creating and maintaining them. Below are the reasons why we felt separate accounts are clearly the way to go...
Advantages
- It ensures the integrity of the deployment as dev accounts shouldn't see Test/UAT/Live
- Forces the deployment scripts to be correctly parameterised and helps drive out bugs in the deployment.
- In conjunction with the use of database roles there is no additional overhead for administering database security
- Ensures that events in dev/test don't affect live. For example an account getting locked out in live because a user has failed to login correctly to the account in dev and breeched a group policy.
- More likely to result in a scripted and repeatable build/deployment process which can be re-used in the event of catastrophic failure
Disadvantages
- Overhead in creating all the accounts in the first place
- Additional development work to parameterise all the build scripts
- Can be seen as over-kill by some who view some of these steps as one time only operations.
Anyone care to add / question anything on this list?
Cheers, James