A Wandering Mind

I am a very firm believe that only a DBA should have sysadmin rights on a SQL server instance, including in development systems. The fact of the matter is that in the majority of cases, system administrative rights are nothing but overkill; developers don’t need (nor in many cases understand the how / why) to do things like change sp_configure settings or alter database level settings like auto_shrink (don’t get me started). What they do need is this:

• Full control over their own database (db_owner). This allows them to:
  1. Backup up the database before applying changes
  2. Change schema and security at the database level
• The ability to create and remove logins (but not system administrator logins) for use by their applications.
• The ability to restore a backup of their database to roll back changes as part of testing or development.

In my environments I give developers this basic set of rights by giving them these permissions:

1. db_owner on their own databases
2. db_creator at the server level

   When I give them this right it is for restoring their own backups, not for creating databases willy-nilly. This is explicitly communicated to them and violations result in revocation of this privilege.

3. ALTER ANY LOGIN at the server level

   See here for why I didn’t say securityadmin.

In most cases this takes care of the developer’s needs and I don’t get complaints. But from time to time, special requests come up. Yesterday I has one such item, where the developers wanted to be able to using Database Tuning Advisor and the built in SSMS reports such as the “Schema Change” report. Naturally, their request was for sysadmin rights. Not convinced, however, I looked into what was actually needed.

Database Tuning Advisor

In another case of RTFBOL, here is a clear explanation of what rights are required. Basically, a user with sysadmin rights has to initialize the system first. After that, any user with db_owner rights can use Tuning Advisor.

Built-In SSMS Reports

While I couldn’t find any built-in documentation on this subject, I was able to run a trace while running several reports (inlcuding index usage and schema changes). What I found was that the reports were doing one of two things: 1) querying various management DMVs, or 2) reading the default trace using fn_get_tracetable. Bases on these findings, I granted the VIEW SERVER STATE and ALTER TRACE permissions. Problem solved.

So no, Mr. Developer, you still can’t have sysadmin rights on my box.

Be Sociable, Share!

•