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