Efficient ways to reset lost sql sa password

Question

Efficient ways to reset lost sql sa password

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

wayne.mcdaniel (10/25/2010)
Jeff Moden (10/24/2010)
yitiana (9/19/2010)
Have you ever forgot or lost your SQL Server sa password?
I'm always amazed at how many people do such a thing.
Probably more likely that the one person who knew quit/died/got fired/etc...

That's why I take safe-guards against such a thing. I don't let anyone login or use the SA login. I'll have two DBA's each make a password and combine them as one. Neither DBA knows the other password. The two passwords are then sealed in envelopes and put into two different non-IT safes (sometimes, off-site, depending on the company). I know it won't stop a malicious DBA from changing the password but it's otherwise a good safety net.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Erik Bitemo SSCrazy Points: 2062 More actions · Answer 2

The easiest way is to shut down SQL Server, start it up in single user mode (sqlservr -m) and in this state OS admins will get sysadmin privilege, no matter if they have it normally or not.

Cheers

-- Erik http://blog.rollback.hu

Perry Whittle SSC Guru Points: 233858 More actions · Answer 3

when you install SQL Server 2005 it creates a series of groups on the windows server, these groups are provisioned into the SQL Server instance. Add your windows account to the SQL instance group and this will provide access, or just run SSMS as the SQL Server service account (you did use domain user account for your instance didn't you!! ).

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

duocaiduoyi2009 SSC Enthusiast Points: 139 More actions · Answer 4

Thanks a lot for your guys sharing.

i am a newbie, i would like to recommend one software named SQL password recovery to those novices.

Just several clicks, you can reset your lost SA password.

😀

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

duocaiduoyi2009 (6/28/2011)
Thanks a lot for your guys sharing.
i am a newbie, i would like to recommend one software named SQL password recovery to those novices.
Just several clicks, you can reset your lost SA password.
😀

Just to be sure so no one gets the wrong idea... Unless I've totally missed something on that site, despite the name of the website, that product does NOT recover the old password. It will, very effectively, allow you to assign (reset) a new password but it will not recover the old one.

If you need to know the old password for some reason, that product isn't going to do it for you and neither will most methods posted on the internet.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

wayne.mcdaniel (10/25/2010)
Jeff Moden (10/24/2010)
yitiana (9/19/2010)
Have you ever forgot or lost your SQL Server sa password?
I'm always amazed at how many people do such a thing.
Probably more likely that the one person who knew quit/died/got fired/etc...

Just for that reason, at a previous employer I had written down the SA passwords, put them in a sealed envelope, and had it secured in a locked location. Our PeopleSoft Admin did the same thing with her PeopleSoft admin account. I was the only DBA on the team with the SA password and she was the only member of the PeopleSoft team with the PeopleSoft admin password.

The DBAs had logins with sysadmin rights, so no need for the SA password. I also had a SQL login with sysadmin rights in case something happened to my windows account. Basically, I made sure I hade three ways to can access to any of our SQL Server instances.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

I do much the same, Lynn. The only problem I've not been able to solve is if someone with SA privs gets really ticked off and changes the password for all the SA-enable accounts or simply deletes the logins.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Perry Whittle SSC Guru Points: 233858 More actions · Answer 8

It's surprisingly easy to gain access to a SQL server instance. In my previous post to this thread there are details of how to accomplish this. Also some one has mentioned starting SQL server in single user mode.

If you have physical access to a SQL box it's game over.

It shouldn't matter resetting an SA password, unless of course you have an application using it for authentication!

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

paul.knibbs SSCoach Points: 15320 More actions · Answer 9

Jeff Moden (7/2/2011)
I do much the same, Lynn. The only problem I've not been able to solve is if someone with SA privs gets really ticked off and changes the password for all the SA-enable accounts or simply deletes the logins.

As Perry says, can't you fix that by starting SQL in single-user mode and using the emergency connection? It's a royal pain in the nether regions and you obviously lose access to your databases while it's running single-user, but if you're absolutely at a dead end and can't do anything else it ought to work!

Perry Whittle SSC Guru Points: 233858 More actions · Answer 10

don't give out SA privileges 😀

I ask them to explain why they need SA, when they can't give a valid reason i use that as my push back 😉

It's a fair request, if someone asks for administrative rights over your SQL Server you're more than justified for an explanation as to why IMHO.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Eric M Russell SSC Guru Points: 125522 More actions · Answer 11

Perry Whittle (7/4/2011)
don't give out SA privileges 😀
I ask them to explain why they need SA, when they can't give a valid reason i use that as my push back 😉
It's a fair request, if someone asks for administrative rights over your SQL Server you're more than justified for an explanation as to why IMHO.

Often times when a QA engineer, manager, BI consultant, etc. requests "SYSADMIN access", what's going on is that they are using some tool (SSIS, Red Gate schema comparison, SQL Profiler trace, etc.). When that fails, they assume that SA rights are needed.

However, there are built in SQL Server roles and permissions for things like managing SSIS (db_dtsadmin), viewing schema (VIEW DEFINITION) or running profiler traces (ALTER TRACE), or querying dynamic management views (VIEW SERVER STATE).

You don't need to grant them SYSADMIN membership, which would unnecessarily allow them full control of the server.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho