Hey, I need sysadmin access to ServerA.

Ok. Why do you need sysadmin?

Well I need to be able to read and write to all of the tables on DatabaseA.

No problem. I’m going to add you to the db_datareader and db_datawriter roles on DatabaseA. Done.

So, why do you need sysadmin?

I also need to be able to see the code behind any object. And not just DatabaseA but any database.

Ahh, ok. So that would be VIEW ANY DEFINITION at the instance level. Done.

So, why do you need sysadmin?

I need to be able to access the DMOs (Database Management Object) related to performance?

Not a problem. Do you need to be able to access them at a server level as well as a database level?

Yes?

Ok, so we will grant you VIEW SERVER STATE at the instance level. If you didn’t need the server level DMOs we could just grant VIEW DATABASE STATE on the databases you need it but this will cover both. Done.

So why did you need sysadmin?

I really need to be able to create new databases.

Ok, so now we have something interesting. This is a development environment so I’ll grant the permission to you by adding you to the server level role dbcreator but with some warnings. Had it been a production environment that’s probably not something you would have gotten.

Why?

If nothing else because I don’t get paged out when something gets broken in development. The ability to create new databases (and alter, drop, or restore them) is pretty powerful. You could easily create a database that’s too large for the drive, causing it to fill it up unexpectedly. Or accidentally put tempdb on the C drive.

Is that bad?

Yes

Ok, I’m going to stop here. There are lots more common permissions we could go through and in almost every case I can name you a permission that doesn’t require sysadmin. Sysadmin is a high level permission that covers every possible permission on the instance. Including things like shutting it down, dropping databases, changing settings etc. The vast majority of these permissions are not needed or not used frequently. Honestly the main reason that DBAs typically have sysadmin is because it’s a pain to grant all of the individual permissions required to do our jobs. In fact at my current office I have a general access id that let’s me do 90+% of all of my work and a sysadmin id that I only use on the rare occasion I need to do something beyond what my regular id has. And of course there is always that rare case when something actually does require sysadmin.

So next time you think “I need sysadmin”, or someone else tells you they do. Just remember, except in very rare circumstances (in house only, never a vendor) they don’t need that level of access.

Note: The same thing goes for database owner access at the database level. Different permissions obviously but the same principal applies.

Filed under: Microsoft SQL Server, Security, SQLServerPedia Syndication Tagged: security