TSQL Tuesday #63 – How do you manage security: Rollup


February 12, 2015 by Kenneth Fisher

When I decided on security as my topic for February’s T-SQL Tuesday blog party my thought was that security was a topic everyone would have something to say about as it’s something that touches the lives of every DBA at some level or another. Well it turns out I was right. We had a great turnout with lots of very interesting posts on a huge range of security topics. If you want a good overview of security in the SQL Server world you could do worse than reading these posts. In fact you could almost write a book using just these posts. So I did.

SQL Server Security by the Community

Chapter 1 – Overview

Sebastian Meine (b/t)
Sebastian freaked me out for a second with his frightening blog title You’ve been hacked! This is at once a frightening overview of why we need security and a high level overview of how to fix it. I particularly like the onion.

Edwin M Sarmiento (b/t)
I have only one thing to say about Edwin’s post. What do we frequently forget? The Human Factor.

Chapter 2 – Physical Security

Paul Randal (b/t)
Paul picked a topic I don’t see very often. Securing the data technologically is important. And you certainly don’t want to forget the human factor. But to be truly secure you also can’t forget the physical side of security.

Chapter 3 – Database Engine Security

Section 1 – Privileged accounts

Boris Hristov (b/t)
Boris’ post on managing security discusses that all too common horror story of a vendor that requires sysadmin permissions. He then moves on to discuss the risk of NT Authority\SYSTEM. (And if you don’t already know what that risk is you really need to read this one.)

Andrea Allred (b/t)
Andrea may be known for her princess gowns and sweet demeanor but based on her first official T-SQL Tuesday post she doesn’t need any Prince Charming to step in and save her servers. Her advice is actually something I’d read about recently and I really love the idea. If you’ve ever wondered what to do to protect yourself from malicious use of sa you need to read her post.

Kenneth Fisher (me)(b/t)
For my own contribution I discussed the reality of denying permissions to db_owner vs dbo.

Jason Brimhall (b/t)
As the SQLRNNR Jason delivered a few quick (get it runner/quick?) but very effective case studies on the over use of privileged accounts.

Section 2 – Using roles

Steve Jones (b/t)
Using database roles has been considered a best practice as far back as I can remember. In his post Steve gives us an example from his past of why this is so important and the steps they took to implement it.

Jim Dorame (b/t)
In another take on roles Jim takes his grouping layer out to the server itself.

Section 3 – Dealing with Logins

Robert Pearl (b/t)
Robert mentions a couple of very handy bits of security information. First, if you are using SQL authenticated logins there are some settings you had better be aware of. And second he gives us some handy T-SQL for looking at the current status of the logins on a server. It also turns out that Robert’s upcoming book (quick plug here) HealthySQL will contain even more security goodness.

Mike Fal (b/t)
Mike discusses setting your default database as a good best practice (and I tend to agree). Is anyone surprised that he decided to give us a powershell script to change it?

Section 4 – Coding

Rob Farley (b/t)
Rob discusses SQL Injection in his excellent post SQL Injection – the golden rule. In it he describes a way to think about SQL injection that makes it much easier to tell if you are vulnerable and to avoid that vulnerability in the first place.

Section 5 – Monitoring

Daniel Mellor (b/t)
Using Policy Based Management to review your security setup is something I’ve been looking at recently myself. In Daniel’s post on the subject he answered one of my current questions and has set me on the road to solving something that has been bugging me. Expect to hear tweets on the subject Daniel. I should also mention this is his first time joining us on T-SQL Tuesday. So thanks for joining in!

Section 6 – HA & DR

Warwick Rudd (b/t)
Are you using AlwaysOn Availability Groups? If so you need to read Warwick’s post on making sure the security of your replicas continues to work when you fail over.

Angela Henry (b/t)
A server going down is never fun. When that server is your payroll server and you are on a hard deadline it’s even worse. Angela relates a “somewhat embellished” story from her own career and the security ramifications of restoring your databases on a new server.

Chapter 4 – BI security (SSAS & SSIS)

Koen Verbeeck (b/t)
One aspect of security that tends to trip up DBAs and developers alike is that of the SSIS protection levels. Koen’s post gives a great rundown of the protection levels for SSIS packages and when to use each. He also keeps us up to date by going over some of the changes made to this aspect of SSIS in 2012.

Jens Vestergaard (b/t)
In Jens’ debut T-SQL Tuesday post (way to go Jens) he gives us a rundown on SSAS security and a tool called Forefront Identity Manager that can be used to manage it.

Capter 5 – Key Management

Russ Thomas (b/t)
Russ gives us a great breakdown on why EKM (Extensible Key Management) is probably part of our future.


Chris Yates (b/t)
In summary security is something with a lot of moving parts that we have to pay attention to. There is a mindset needed to keep your data secure and Chris Yates does a great job letting us know where our heads should be when it comes to security.

2 thoughts on “TSQL Tuesday #63 – How do you manage security: Rollup

  1. […] Feb -T-SQL Tuesday #63 – How Do You Manage Security? (roundup) […]

  2. […] Wait. Sorry, that was last time. This time I want to hear about backup and […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,746 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: