Given the general mayhem and wanton destruction database developers can inflict on an organization, you'd think there'd be a best-practices set of SQL Server permissions that DBAs should grant developers. Or better yet, a "dbdeveloper" server role that falls somewhere between "public" and "sysadmin." But I haven't seen either one.
I'm working with a client whose developers have membership in the sysadmin server role (bad enough) or know the sa password (couldn't be worse). I need to trim those permissions back to a level that protects the servers and data without making it impossible for them to do their jobs.
It's only slightly misleading to imply that there are no best-practices guidelines. The National Institute of Standards and Technology will point you to the Defense Department's database "STIGs" -- Security Technical Implementation Guides. The SQL Server 2005 STIG (Nothing yet for 2008? Really?) is a 300-page checklist for hardening a database server.
These guidelines aren't really reasonable for organizations that aren't guarding state secrets. DOD treats developers as if they were Chinese hackers: the enemy. STIG DG0089 captures the attitude: "Where restricted resources prevent the required separation of production and development DBMS installations, developers granted elevated privileges to create and manage new database objects must also be prevented from actions that can threaten the production operation."
If you've never STIG'd a database server before, it's a real treat. But my client's not DOD, it's a small staff, the three developers need enough leeway to do their jobs, and one of them needs to be able to act as a semi-DBA on the production servers.
Using SQL Server 2012 would make this a bit easier, as it gives you the option to create custom server roles. But we're on 2008 R2 here, so we'll have to do it the hard way. Here's what I came up with:
First, we don't want individual logins in SQL Server. We want to create logins for Active Directory groups. That simplifies administration in a number of ways: If someone leaves the company, you only have to disable their Windows login in AD instead of tracking down every login in every instance of SQL Server. And, of course, you only have to set permissions for a group once, instead of for each member of the group. I've set up four groups: SQLUsers, SQLDevelopers, SQLDDLadmins, and SQLsysadmins.
SQLUsers get the public server role.
SQLsysadmins get the sysadmin role.
SQLDevelopers and SQLDDLadmins are essentially the same thing, but the latter gets elevated permissions on production servers.
Here's the security setup for those two logins:
At the server level, they're members of these roles:
bulkadmin, which lets them run bulk insert statements
dbcreator, which lets them create, alter, drop and restore databases
diskadmin, which lets them manage disk files
processadmin, which lets them kill a running process
setupadmin, which lets them create linked servers
Sounds ample, but we're not there yet. Developers need some access to the SQL Server Agent, and they need to write stored procedures. In some environments, the devs would have to ask the DBA to install scripts and set up jobs on the agent. But that's a huge pain for everyone involved, and you waste a lot of time waiting for other people to do stuff that you want done RIGHT NOW.
So I created a user in the model database, mapped it to the SQLDevelopers and SQLDDLadmins logins, and granted it CREATE PROCEDURE. When a dev creates a database, he or she will be able to write procs against it. They will not, however, be able to insert procs into system databases. Other database permissions, some of which may be added as we test the new regimen, are listed here.
I also made the devs and DDLadmins members of the fixed SQLAgentOperatorRole. You do this by mapping the login to a user in the msdb database, going to the user properties and selecting the appropriate agent role. Now the logins can see the agent, but they can't do too much damage: they can't change the owner of a job they create, so nonrepudiation isn't an issue. And what happens in dev stays in dev -- they can't use the agent to stage their nefarious raids on production servers.
And finally, I disabled the sa login. If someone needs sysadmin privileges, that's one thing. But if one non-DBA employee knows the sa password, you can bet that at least two non-DBAs have the sa password. If someone logs in as the sa and does a bad thing, you'll never be able to prove who it was.
Are these best practices? I dunno. With the dbcreator server role, they can delete databases they didn't create, so we might dial that back in testing to an explicit grant to "create database" or "create any database." But if that causes too many calls to the DBA we might stick with dbcreator.
Is it really necessary to restrict developer permissions to this extent? Well, security's a critical part of a DBA's job -- and this client stores Social Security numbers. Also, if your dev and prod servers are on the same network, a developer could take down production without thinking too hard about it.
I know what you're thinking. *Our* developers would never do something like that! Well, you don't have to look far to find evidence that they could (here, for example: "82% of large organizations reported security breaches caused by staff," according to a 2012 information security survey by PricewaterhouseCoopers). Losing your data isn't worth giving in to elevated-privilege pleadings from people who write code.