SQL SERVER – Forgot the Password of Username SA

I just received a call from an old friend with whom I used to work in Las Vegas. He told me about a password-related issue he faced in his organization. They had changed the password of username SA and now they are not able to recall the new password. I am sure that he is not the first person who has faced this issue. There may be many more similar situations where employees who have sysamin password leaves the job or a hacker disables the SA account.

Note : If you have SQL Server Agent enabled, it starts before SQL Server service. If you have enabled SQL Server in a single user mode, it will connect it first, so it is recommended to turn that off before attempting any of the above options.

Consider there are .bak files for more than 2 years old in a directory , if i create a Maintenance plan and set delete files older than four weeks then it is not deleting last year files, please suggest your opinion.

In case of option 2, any one with administrative privileges to the server computer on which SQL Server is installed is included in windows admin group. It may be a single account or any windows group added to windows admin group.

It was very nice to read your article on how to reset the “SA” password in SQL Server.

While I myself am a strong supporter of the Windows Authentication feature; I do understand why many installations and applications still need the “SA” login for some system setup tasks. This is especially true for legacy systems, like those which migrated from flat-file systems like COBOL and QUADBASE.

I do use the Option# 1 frequently to reset the “SA” login password, however your note at the end of Option# 2 completely threw me off; and I would like to have some more insight on the following line of thought.

SQL Server Agent service is dependent on SQL Server – however, it does start before SQL Server. That would explain why sometimes, the SQL Server Agent remains (or seems to remain) running even if SQL Server itself is off. I had noticed this in many MS Webcasts on SQL 2005 and have also exprienced this myself – more frequently on servers running on Virtual Machines. While MS would have a very specific reason for keeping it this way, doesn’t this look like a defect? Also, what architectural advantage would be obtained by starting a “child” before the “parent”?

It would be wonderful if you could address the above in one of your future posts.

Thanks. This works fine for a default instance. Mine’s an R2. However, I dont know how to do it for my named instance. there is only the sa user in there, no other user part of the sysadmin group. Do you know what i can do to try this on a named instance?

Couple of notes for my situation to get it working for SQL Server 2008R2 I used option #2 detailed above, and followed the link to setting up in single user mode, and had a slight modification to the startup parameter

-m;-dC:\Program Files….. (basically adding the – in front of dC, as opposed to screenshot)

Also, I had to stop all the other SQL server services (Sql agent, reporting, etc…) as they would give me an error if they were still running when I tried to connect via sqlcmd.

Another thing was that when clicking on the command prompt, I right clicked it and selected run as Administrator and ran the following command: (Replacing Domain\Alex_Martinez with your desired user name of course)

EXEC sp_addsrvrolemember ‘Domain\Alex_Martinez’, ‘sysadmin’; GO QUIT

At this point I was able to login with the new sysadmin role above and change the SA password via Mangement Studio. Hope this helps another newbie like myself.

Sir, my question is if the situation is where window authentication mode is disabled and their is only a single login account sa and in this condition we forgot password than how can we recover password and gain access to the account.

Pinal Dave is a technology enthusiast and an independent consultant. He has authored 11 SQL Server database books, 21 Pluralsight courses and have written over 3800 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 14+ years of hands on experience he holds a Masters of Science degree and a number of database certifications. For any SQL Server Performance Tuning Issue send email at pinal @ sqlauthority.com .

Nupur Dave is a social media enthusiast and and an independent consultant.