Who can get you fired? Run our free sp_Blitz stored procedure on one of your production servers and pay particular attention to the Security section of the results. It lists the logins who have been granted the sysadmin or security admin roles.
Don’t think of them as logins.
Think of them as people who can get you fired.
These people can drop databases, drop tables, change stored procedures, edit data, or even change SQL Server configuration settings like max server memory or maxdop. You’re probably not getting alerted when any of these things change – we just can’t afford to monitor every single thing in SQL Server and send alerts on it. At some point, we have to be able to trust certain logins, and that’s where the sysadmin and security admin roles come in.
Except when we first get started learning database administration, it’s usually because we’re the only DBA in the shop, and the server is a mess. The front door is unlocked, the key is under the floor mat, and everybody knows we’ve got a big screen TV in the living room.
How to Get Started Locking Things Down
Before you start removing people’s SA rights, be aware that there can be political backlash. In one shop, the head developer’s SA rights were removed, and he stormed into the DBA’s office the next morning screaming. Turns out one of his apps automatically created a processing database every night, did a bunch of work in it, and then dropped the database. Nobody knew because it was only around for 30-45 minutes. The problem could have been avoided by communicating the security changes ahead of time, and that’s where we need to start.
Take the sp_Blitz output to your manager – just the security portions – and say something like this:
“Here’s the list of people who can do anything on the production server – delete data, drop databases, or change performance settings. If they do, here’s the list of applications that will be affected, including accounting and payroll. I don’t want to take away all of their permissions – I just want to start by giving them full permissions over their database, but not in any other databases, and not at the server level. Can I talk to them about doing that?”
Note that we’re only going to TALK to them, not actually do it, because we need to communicate with them first. Then, go to the end users or developers involved and say:
“We’re starting to lock down the production server, but I want to make sure you have all the permissions you need. I’m going to make you a complete database owner inside your database so you can do anything you want in there, but I’m going to take away your rights to the other databases (like accounting and payroll), and I’m going to remove your permissions to change server-level settings like how much memory the server can use. I’m planning on doing it next weekend, and I want you to have my email and phone number so that if anything breaks on that date, you can call me and I can audit what’s happening on the server to see if it’s related to the permissions change.”
When You Get Resistance
If you get any pushback from developers or users, go to the project managers or business people who have a large stake in the database. For example, if the accounting database is on the server, go to the CFO and say:
“Here’s the list of people who can take down the accounting system. They have the permissions to drop the database at any given time, and there’s nothing I can do to stop it. I’d like to get that changed – can I schedule a short meeting with you and the development manager to get everyone on the same page?”
You want to turn it into a business problem, not a technology problem, and the CFO will very much be on your side. She can’t afford to have her entire department go down just because some developer didn’t put a WHERE clause on a T-SQL statement.
If you want to have a little fun with it, read my post, How to Talk People Out of the SA Account, and the part 2 followup with a vicious sense of humor.
I Know, This Week Isn’t Fun
Database administration isn’t all candy and roses. Sometimes it’s boring politics and paperwork, and this is one of those weeks.
In the first week, we built a spreadsheet inventory of our servers, and now it’s time to fill in a little more details. Since we’re analyzing security, we need to know which applications live on each server, and who’s in charge of each of those applications. You don’t have to fill in the specifics of who has read or write permissions in each database, but we want to at least know the business purpose and the business contact.
The business contact is the one who really helps us get the database locked down because their job is on the line if this data is lost or unavailable. (Come to think of it, you’re someone who can get THEM fired!) In the coming weeks, you’ll be working more with them on reliability and performance, too, so now is a good time to start fleshing out that part of the spreadsheet.
Next week, we’ll move on to something you DON’T have to do: checking for free drive space. No, seriously!
12 Comments. Leave new
Just wondering, do you know anyone who was fired because of this?
As a brief anecdote, one former employer in my youth hired this hotshot performance guru. He initially had some quick wins, until he put _every database_, not just tempdb, on RamFS. The company lost a week of data after a weekend reboot. The company I worked for didn’t fire him.
That’s when I started to realize the firing tactic is more of a temperament and personality thing than anything else.
John – indeed I do, but that’s about all I can say about that one. (No, it wasn’t me, hahaha.)
Putting the ultimate decision on the business stakeholders for which accounts require elevated permissions (with the associated risk) is the right way to go. It is a business decision, not a technical decision. And in my experience is often driven by business’ choice of 3rd party applications and those application requirements.
If the 3rd party app has those elevated needs, we typically will isolate it on a smaller VM instead of a larger horsepower shared server. If they screw something up, it will be limited to their own app. The VM cluster is licensed, so another VM is no big deal for us, but YMMV.
That could limit exposure to data located elsewhere but if the app is your central accounting system the elevated permissions – even limited to that one app – are still a huge risk to the business.
@john
We had a new DBA who removed elevated permissions on a number of logins without first discussing with business and technical stakeholders- not surprisingly those unannounced changes brought down a few business critical applications in the middle of the business day, and of course damaged working relations between that person and business stakeholders. He did leave not too long after. So one does not need to get fired by management to end up with a job change after a faux-pax.
Bet he got a raise when he left
He used me for a professional reference and I gave him a good one. However the organization we worked for at the time was always rolling in cash, so I am not so sure he got a raise at his new company.
Working at an insurance company I get these kinds of conversations all the time, so here is a more bureaucratic way of looking at it… Like Brent said I think the best thing to do is don’t be nasty and pre-judgmental about it. Try to do investigate with some traces and figure out why access is needed. Break it down into business requirements. Then give the owner of the process (like the CFO) a quick summary prompting them for a sign off.
After such a conversation unneeded access is often culled, and those that need access are given a sign off until an automated solution can be put in place.
If you have the support for it, you can take it to a next level with regular reviews of privileged access and monitoring of security changes. Proof that you did the review and are doing the monitoring is great for auditors and covering your liability.
I’ve come across FAR more political problems trying to claw ridiculous permissions back than anything bad users have done with their excessive permissions. Even presenting the problems with unneeded permissions I have gotten heat for.
When a problem causer is buddies with the CFO and hangs out with them on the weekend, it can take an outage or loss of data to have their permissions taken away – right after the DBAs put in a ridiculous amount of work cleaning it up. Maybe over a weekend.
I’ve had more luck building new SQL servers to migrate into, being the SQL nazi (seinfeld reference) about permissions and then extracting a portion of the data out to the old to keep the problem users placated. though I admit on one box we turned a job off for maintenance that was keeping one of my honey pots up to date, then forgot about it for 10-11 months before the problem user noticed.
Beyond all the really bad things that can happen, all the random stuff that breaks constantly when end users have been empowered to query SQL servers, when their select * from a busy OLTP server breaks because another column was added in an application update.
Keith – that’s all very well said, and I agree totally! I’ve had exactly the same experience.
We have a similar, but bloated access problem. Permissions in general. Our ‘sysadmins’ groups are ship-shape now, but in past migrations, prior DBAs have simply carried all applied permissions forward from box to box for years, probably all the way back to SQL 2000 or 2005. We have a massive number of AD groups (500+) with little to no reference as to what they were actually for. Determining how a user actually gained access to a server is a problem, because they may be in 10-15 groups that all have some level of access to the same DB. And in those same groups there are nested groups, and tons of users who never actually use SQL for anything.
We are consolidating groups slowly, and conducting reviews when servers migrate now, but it is not a fast process. And I haven’t found much useful help in any 3rd party tools. We are now tracing successful logins to see who is actually connecting and will eventually collect enough data to make some informed decisions.
Moral: Clean up and review your security periodically, people!! 🙂