SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Balancing Security Concerns.

I have a presentation I do every now again on security basics that I’m actually quite proud of. One of the sections at the end is on best practices, where I mention a few important security best practices broken down as follows:

  • Least Maintenance
  • Least Least Surface Area
  • Least Privilages

Probably my favorite part of the whole thing is the first and last best practice I mention.

  • Don’t make permissions more granular than you have to. (Don’t grant permissions at a table level if granting them at a Schema or even better the database level will work)
  • Grant permissions at the lowest level possible. (Don’t grant permissions at a database level if granting it at a Schema or even better an Object level will work.)

Then I sit and wait a few seconds (depending on how much time I have left) and watch it slowly dawn on people what I’ve said. How does that work? How can you have to completely opposing best practices?

Because security is a balance. On the one hand, you have to protect the data. That’s probably our most important task. On the other hand, our users have to be able to do their jobs. So we have to grant them sufficient permissions to do so. And on the other hand, we have to be able to do our jobs. If we are spending all our time dealing with security we aren’t going to be doing anything else. (Yes, that’s three hands, so sue me.) Maintainability is a must.

In other words it depends.

  • If you have hundreds of servers and thousands of databases with active development on many of them, you probably can’t manage security at an individual object level.
  • If you have a database with highly secure data, say some tables have payroll information or HIPAA related data. You need to be more careful here and might need to grant permissions at an individual object level.

Really, in the end, it’s going to come down to your situation. You need to make your security as tight as possible, while still allowing your users/developers/customers etc to still do their job, and still manage security over time. Of course, the more you know about security the easier this gets but you still need to make sure you pay attention.


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...