I’m going to be very blunt here: Nobody is sysadmin but me on a SQL Server instance. With “me” I mean the DBA group that has to be able to work on the instances of SQL Server.
Do you recognize this situation:
A company application that stores mostly private information of their customers and everything runs smoothly. Than suddenly you get a security audit and you discover that the application has GOD mode on the instance. The auditor asks you why and you don’t have your story ready. The auditor writes down something and leaves. A few hours later your manager comes along, sweaty, fear in his/her eyes and asks you why you didn’t do your work for being in control of the server. “Why didn’t you screen the database servers for security implications!”.
This is what happened to me and I was only there for about 6 months, but being the only DBA I was the one to go to. From that moment on I made it a mission on database servers to make sure that no account but the DBA group was sysadmin on the instance.
Why was the server set up this way?
What went wrong during the installation of the application was that the software vendor couldn’t give a straight answer why the application didn’t install with normal installation right like db_creator. In a perfect world the software vendor supplies a set privileges needed for the installation and a set of privileges needed for normal functioning of the application.
In this situation this was not the case obviously and the DBA at that moment just gave SA to the service account.
But why is that a bad thing? I’ll give you a few examples:
- Nobody on the instance need to be sysadmin except admins…. period 😉
- If you’re sysadmin you can do anything in SQL Server, no restriction
- All security check are bypassed
- No software is hack-free
Imagine that you come in on Monday and suddenly and due to budget cuts or other priorities your application is not up-to-date. I’ve had situations where the vendor went out of business but the application was needed for several business-critical processes. Suddenly employees come to the administrators telling them that there is no more data in the application. You look at the databases and the database is gone, or all the tables are deleted, or even worse the application could connect to the internet and suddenly all the data is available online.
But what if the software vendor doesn’t know ( or want to know ) how to make the accounts work with less rights. I assume you’re having a test-server where everything is setup as in production. Just remove all the rights and only give the minimum read ( and possible write ) permissions. Let the users test the application and adjust where needed.
But what if I have a certain group of people who need more rights and sysadmin is easiest way? Lucky for you since SQL Server 2012 you’re able to create your own server roles. That means that you can setup a role with for example the “view server state” right to let other users see what happens on the server. Mike Walsh created a nice example how to give junior DBA’s the tools to do the work they need to do.
I’ve been in situation where project managers were desperate to complete their project but with an ugly security model in place. In the past, I must admit, I sometimes caved due to the pressure to complete the projects. Don’t let this happen!
When the project is complete you’re responsible for the database server, with or without security in place like it should, and when push comes to shove you have to explain why you accepted the database server as it is and why you didn’t act to get things right.
But I don’t know this server, how do I see which users have too much rights?
I’ve used the script from the following article for a while now and it helped me a lot to get a fast overview of the database and server permissions set for each user or group.
Hope this helped you out!