You don’t need to be a sysadmin
27March 10, 2016 by Kenneth Fisher
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.
SQL Studies 
Nice post, full of great examples, Kenneth. I tend to get rant-ish when my blog wanders into sysadmin and/or db_owner territory. Kudos for remaining calm. 🙂
Actually that was a rant 😀 Just a rant with a sense of humor. One of these days I should make a menu option for “rants” or at least a category. Glad you liked it 🙂
Oh, man. Loved this post. You could also add telling software vendors they don’t need sysadmin rights. They would say the app doesn’t work unless it has sysadmin. Many times I simply ran sp_helprotect against the database the product installed to find NO permissions had been granted. Added db_datareader, db_datawriter, and EXECUTE for all stored procedures and they were amazed that their app worked.
BTW, I had to tell the DBAs at my previous job that “You don’t need to be sysadmin all the time”
http://www.sqlsaturday.com/489/sessions/details.aspx?sid=42784
Thanks! Looks like a great session. And yes, I was including vendors in the post :). I have seen one or two systems that didn’t work OCCASIONALLY without sysadmin. We granted them lower level permissions and then when they actually needed to do an elevated function granted sysadmin temporarily, removing it when they were done.
Ah, Ron, Ken…with the death of SQLPASS, the link is gone to (http://www.sqlsaturday.com/489/sessions/details.aspx?sid=42784)…any chance you guys have that script or material still floating around, please?
I’m looking to build a custom role for a DBA, without having to give SysAdmin due to Audit and internal Sec-Policy…
My pet-hate are developers that create an application with a user with sa or dbcreator rights. Usually I inherit these DBs. Testing the application to find out if it needs more than just db_datareader and db_datawriter rights is a pain in the ass.
When I have the chance, I specify that the application user only have execute rights tio the necessary stored procedures, but since the developers have insisted upon using Entity-Framework, then the application user has to have more general rights.
Unfortunately, in the real world, I frequently find that vendors don’t know exactly what they really need, just that they’ve only tested it to work with sysadmin. And then they get irritated when you want to just grant them permissions as necessary and they keep running into problems.
Heck, I worked for such a vendor for a while, inheriting a lot of legacy code. I can honestly say that I *tried* to figure out what permissions we really needed… but since we usually had a dedicated SQL Server for our application, and often sold to people with minimal IT departments, almost everybody was happy to give us sa. Which kind of reinforced the cycle where developers didn’t give a lot of thought to how the database end worked.
As IT becomes more and more data(base)-driven I hope we see a cultural shift on this.
Nice post. As we deal with many vendors, some regularly requesting sysadmin, we tend to simply build them their own servers to avoid them taking down anything else when they mess up.
I have seen vendors building an installer that required the “sa” account with a specified well-known password. I have even had one that wanted to place *.mdf and *.ldf directly in “C:\Program Files”.
b.t.w. In our company we have gone a lot further than this article describes. We have build a complete set of “Self Service” procedures by which the developers can create they’re own databases, add logins and users, make backups of the databases etc. without any sysadmin or dbo rights and only in the way we want it to be done.
Yep. Some vendors just don’t seem to get it. And yea, I’ve seen stuff like that too 🙂 I’ve got one setup where they have a custom “model” database that they can copy, modify, and then backup up the copy to send to a vendor.
I do have a case were I’ve tried all your suggestions but still cannot make this third party software to run without the user being a sysadmin. As of today we have 25 active sysadmin users in our server. The vendor insists they’re working on it, but years have passed without a solution. Any suggestions.
Honestly that can happen. Really the only thing you can do is isolate them. Put them on their own instance. It’s one of the primary reasons for having multiple instances (to segregate security). That way they can only hurt that one application.
Beyond that you can put some logging/auditing in place to see who did what. Take regular backups & test them in case you need to restore out of some damage someone causes.
Or of course talk to management and see about finding a new vendor 🙂
I insist, Sysadmin is never required. We sometimes hand out sysadmin temporarily so that the installer of the application can do it’s (horribel) work, but after installation we take it back. In most cases that will work. There are cases that it still doesn’t work like we had with SAP (for god sake), the only way to solve an issue like this, is using a SQL profiler trace to check what the application is actually doing. In this case it did some impersonation to a SQL account it created earlier. This is also a privilege you can assign. Case solved. No sysadmin anymore!
I don’t disagree with you that no one but a DBA needs sysadmin, unfortunately there are vendors who insist they do. And yea there are work-arounds but sometimes it’s easier to just give them an isolated environment and let it go. Depends on how much time you have to fight it really.
ok, we have a situation where we are trying to move the SSRS from one server to a new virtual server. We run the install for SSRS only, native mode, then open Reporting Configuration Manager and start going through the steps. We get stuck on Database when trying to use a sql account for credentials or even a windows account that does not have sysadmin permissions. I have looked extensively through Microsoft (and google) for why the credential account needs sysadmin rights. We played with adding all other rights but couldn’t get it to accept anything except an account with sysadmin rights. This was on SQL Server 2012 r2 EE Any thoughts?
Unfortunately I’m not overly experienced with SSRS. I’d suggest posting the same question on a forumn such as dba.stackexchagne or sqlservercentral.com. Put the link here if you do I’d be curious to follow the responses.
It depends on how you perform the migration of SSRS? Do you create a new Report Server database, or use existing? In this article there is a description on how to move you Report Server databases to another “SQL Server https://msdn.microsoft.com/en-us/library/ms143724(v=sql.110).aspx”. And yes, you need sysadmin to perform all these steps, and not only for backing up and restoring the Report Server databases, but that’s where a DBA is for. It does not mean that the (service) account on which SSRS runs needs to be sysadmin during runtime.
Know of any way to grant SSMS Debug permissions without granting sysadmin role?
I’m afraid not. It’s probably the biggest reason that so few people actually use that feature.
can you please share i can i provide debugging permission without sysadmin
Unfortunately there is no way to do that. Honestly that’s why debugging is an almost unused feature.
[…] amount of time, provide access, run scripts where needed, etc. And yes, part of my job is to say No, you don’t need to be a sysadmin. To tell people they aren’t allowed this access or that. To explain to management why data […]
[…] talked about not being a sysadmin here. I realize it’s sysadmin not db_owner but the same arguments […]
I’d love it if there was a script somewhere for creating a custom server role dedicated to DBAs, without having to give them sysadmin.
Sadly you can’t associate database level permissions to a server role and most DBA type rolls are going to require at least some of them. In some cases you can substitute server level permissions “VIEW ANY DEFINITION” for example or “CONNECT ANY DATABASE” but there is no global read for example.
That’s fair enough. The script could include a foreach loop to add db_owner or whatever in each database.
Yep. And permissions in model do transfer to new databases so that would help.