October 19, 2011 at 11:21 am
So, we got a request to drop these two users from SQL Server as part of a corporate security effort.
I've read the post on the BuiltIn\Administrator account but can't see to find anything on dropping SA if that's even possible.
I've renamed it on my test system and that's not a problem but attempts to drop it completely always fail. I've read that it belongs to DBO and as such, is linked to all databases and cannot be dropped.
Just wanted to check here to see if I'm off base with telling them that SA can't be dropped.
October 19, 2011 at 11:24 am
sa can't be dropped. It can be disabled, which means no one can use it. I prefer that over renaming as there's an upgrade bug 2005-2008 if you have sa renamed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 19, 2011 at 11:25 am
Don't know about drop but you can disable the account. Which is somewhat the same end results.
No idea on the impacts it can cause in your system tho.
October 19, 2011 at 11:28 am
Thank you both for the quick response. That sounds like the best path for them to take.
We'll pass along this suggestion and the warning that there may be unknown repercussions.
Thanks again.
October 19, 2011 at 11:32 am
DMarvez (10/19/2011)
Thank you both for the quick response. That sounds like the best path for them to take.We'll pass along this suggestion and the warning that there may be unknown repercussions.
Thanks again.
Can't have unkowns if you test that in QA and that QA is a perfect copy of prod 😉
October 19, 2011 at 11:39 am
I doubt they have a QA system but that's a great idea to pass along.
'Test on a training or test system before deploying to production.'
If they have one of these, it's a close copy of production.
October 19, 2011 at 11:46 am
DMarvez (10/19/2011)
We'll pass along this suggestion and the warning that there may be unknown repercussions.
The only thing that disabling an account does is prevent people from logging in with it. Nothing more. No odd side effects, undocumented repercussions. Please don't start telling people that SQL has odd side effects to documented procedures, that's the kind of press that we don't need (not to mention it starts bad myths and leads to misunderstandings)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 19, 2011 at 12:00 pm
... I was being vague because I wanted you to test (apps or people could be using SA, even if not a good practice).
It's not the kind of change you do it prod without testing it...
October 19, 2011 at 12:10 pm
Ninja's_RGR'us (10/19/2011)
It's not the kind of change you do it prod without testing it...
Agreed, but because of unexpected (and unknown) apps using sa, not undocumented side effects in the engine. That kinf of thing would (legitimately) give SQL a bad name.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 19, 2011 at 12:26 pm
Yeah, I'm a strong believer in testing your changes on something other than production before
implementing them.
Sorry to say that our customers do not always have a test or QA system or a DBA and IT managers often go about changing settings in SQL without thinking about the consequences.
October 19, 2011 at 12:36 pm
I've got the perfect example to that statement.
Our app requires a stored procedure be run after we apply an auto patch. These patches usually replace or create new views or change a table structure.
The stored proceedure drops and recreates our user's and their roles and gives them permissions to any of the new views as well as existing tables, views, etc.
Guess what SQL login you have to be logged in to run it?........SA!
October 19, 2011 at 12:55 pm
For my sa accounts I set it to some random value and let it get changed by another piece of software we have every month, while the password can be determined from that software no process is permitted to EVER use it. NEVER EVER EVER, this is a hard prohibition.
For your case where it needs to be SA, does it actually check and can you change it to simply be that the person must be a member of sysadmin? OR some other user. I generally view any use of sa as lazyness. I don't use it at all. The number of cases where it is absolutely needed are extremely finite so...
Also, just because corporate security wants something doesn't mean it SHOULD happen. they need to explain WHY they want something and then you can either explain a better way or flat out say no because it isn't possible or the outcome will be extremely bad..
CEWII
October 19, 2011 at 1:08 pm
The SP calls specifically for SA. It's old code (1999) that's been removed in our latest version but we stil have a ton of customers on the old stuff.
Corporate Security seems to trump everything these days.
Thanks to everyone for your responses here, as always, a great learning experience.
October 19, 2011 at 1:12 pm
What you can do then is rename and disable sa, then create a low-privilege account (db_owner or lower) and call it sa.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 19, 2011 at 1:22 pm
I have a fair amount of experience with corporate security and almost none of them know shirt about databases. The smart security groups work cooperatively with the other groups. The ones who dictate without cooperation tend to get hammered eventually.
Now, there is a set of generally fairly good practices, they are the DOD STIG documents for SQL. While some of the requirements are impractical in the real world they do get you thinking about security and monitoring, which is a good thing. For SQL 2005 there are only 7 items that are do or die, and like 162 that are basically, you should really look at doing these but they aren't vital, and about 20 more that are like meh...
CEWII
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply