SQL Daily: DBA Training Week 4 - Knowing Who Has Access

Missed last week's email? Read it here.

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 DownBefore 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 ResistanceIf 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.

I Know, This Week Isn't FunDatabase administration isn't all bacon and roses. Sometimes it's boring politics and paperwork, and this is one of those weeks.

In Week 1: Where My Servers At?, 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.

Brent says: Database administration is politics.

Sure, we like to think we're the police, here to protect and to serve, but most of what we do involves sitting in meetings, convincing people to do what we want, the way we want it.

It's made more challenging because we often don't have any real authority. Sometimes the DBAs report to completely different managers than the developers - and sometimes, it's even different companies! We might be administering a database server that houses a third-party application, and the vendor's support team demands to have SA access.