Ima make a deal with the SA so the SA don’t bite no more

T-SQL Tuesday

TSQL Tuesday #63 – How do you manage security?

Yay, so this is my first official blog party post.  I was late with my post last month because I didn’t have it figured out until I saw everyone posting and wondered what was going on.

One of my goals for this year is to increase security across my 50 SQL servers.  The SA log-in is a dangerous one and I don’t want it out there on my servers, the trouble is that it is hard to exterminate. Here is my new plan.

I am going to script out my SA log-in and password using this script.

Next I am going to rename the SA log-in to something else and give it a much stronger password. This information will go into my password safe so that I have it just in case I need it.

I am going to create a dummy SA. I have many vendor databases that claim they need SA, but they really don’t and I don’t want them to have high privileges.  I am going to work on lowering the privileges to only give what they need (this will be a slow server by server process to get the permissions right).  The reason I am keeping a log in with the name “SA” is because of vendors who hard code that user name.

As I was talking to people in the #SLCSQL user group last night, someone suggested that we can also monitor that dummy SA login to watch for attacks. It is a great idea and plan to include monitoring on the new log in.

Lars Rasmussen suggested I have a user on each server that will always be there to handle running jobs and other database needs.  I plan to include this too so that when I don’t have a proper SA log in, I will still have a log in that can handle all my fun stuff.

By doing all of this, my SA won’t bite me anymore.

T-SQL Tuesday is a blog party started by Adam Machanic (b|t) just over five years ago.  This month it is hosted by Kenneth Fisher (b|t).

About andreaallred

SQL Server and helping people is my passion. If I can make someone laugh, I know I have made a difference.

2 thoughts on “Ima make a deal with the SA so the SA don’t bite no more

  1. […] 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. […]

  2. […] 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. […]

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 )

Google+ photo

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

Connecting to %s