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.

Resetting the password of SA is a breeze!

Option 1 :

If there is any other SQL Server Login that is a member of sysadmin role, you can log in using that account and reset the password of SQL Server. Change the password of SA account as described here :  SQL SERVER – Change Password of SA Login Using Management Studio.

Option 2 :

If there is any other Windows Login that is a member of Windows Admin Group, log in using that account. Start SQL Server in Single User Mode as described here :  SQL SERVER – Start SQL Server Instance in Single User Mode.
Create a new login and give it sysadmin permission.

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.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

41 thoughts on “SQL SERVER – Forgot the Password of Username SA

  1. Another great post.. and one I have ran into myself a while ago.

    I do feel however that the sa password is SQL Servers biggest problem, not just because its a hackers favorite, but because non-database savvy application developers use it for their programs.

    We have plenty of 3rd party applications which NEED sa to run when all they do are simple selects/inserts/updates/deletes.

    In my opinion, first thing’s first, disable sa, create a new sysadmin account and save the password in a SECURE location.

    We also use groups to manage windows logins, with 1 group having sysadmin access and only the dba being added to that group. If the dba leaves, another person can be added to the group.

  2. Hi Pinal,

    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.

    SQL Server Version : 2000.

    Santosh

  3. 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.

  4. Hello!

    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,

    Nakul.

  5. hello pinal

    there is one table called Emp in sql server 2005 having two column deptno and employees and i want the output as under means all the employees in same dept comes in one row.

    DEPTNO EMPLOYEES
    ———- ————————————————–
    10 CLARK,KING,MILLER
    20 SMITH,JONES,SCOTT,ADAMS,FORD
    30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

    please reply soon

    • Hi Kulbir,
      Try the following code… It uses a function.


      USE [MyTest]
      GO

      CREATE TABLE Employee (DeptId INT, EmpName VARCHAR(100))
      GO

      INSERT INTO Employee(DeptId, EmpName)
      VALUES (10, ‘CLARK’)
      GO

      INSERT INTO Employee(DeptId, EmpName)
      VALUES (10, ‘KING’)
      GO

      INSERT INTO Employee(DeptId, EmpName)
      VALUES (10, ‘MILLER’)
      GO

      INSERT INTO Employee(DeptId, EmpName)
      VALUES (20, ‘SMITH’)
      GO

      INSERT INTO Employee(DeptId, EmpName)
      VALUES (20, ‘JONES’)
      GO

      INSERT INTO Employee(DeptId, EmpName)
      VALUES (20, ‘SCOTT’)
      GO

      INSERT INTO Employee(DeptId, EmpName)
      VALUES (20, ‘ADAMS’)
      GO

      INSERT INTO Employee(DeptId, EmpName)
      VALUES (20, ‘FORD’)
      GO

      INSERT INTO Employee(DeptId, EmpName)
      VALUES (30, ‘ALLEN’)
      GO

      INSERT INTO Employee(DeptId, EmpName)
      VALUES (30, ‘WARD’)
      GO

      INSERT INTO Employee(DeptId, EmpName)
      VALUES (30, ‘MARTIN’)
      GO

      INSERT INTO Employee(DeptId, EmpName)
      VALUES (30, ‘BLAKE’)
      GO

      INSERT INTO Employee(DeptId, EmpName)
      VALUES (30, ‘TURNER’)
      GO

      INSERT INTO Employee(DeptId, EmpName)
      VALUES (30, ‘JAMES’)
      GO

      SELECT DeptId, EmpName
      FROM Employee
      GO

      –Create Function to Get Employee List in a String by Department
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      CREATE FUNCTION [dbo].[fn_Get_Emp_List]
      (
      @DeptId INT
      )
      RETURNS NVARCHAR(MAX)
      AS
      BEGIN
      DECLARE @return_value AS NVARCHAR(MAX)
      DECLARE @EmpName AS NVARCHAR(100)
      DECLARE MyCursor CURSOR READ_ONLY FORWARD_ONLY LOCAL FOR
      SELECT EmpName FROM Employee
      WHERE DeptId = @DeptId

      SET @return_value = ”

      OPEN MyCursor
      FETCH NEXT FROM MyCursor INTO @EmpName
      WHILE @@FETCH_STATUS -1
      BEGIN
      IF LEN(LTRIM(@return_value)) 0
      BEGIN
      SET @return_value = @return_value + ‘, ‘
      END
      SET @return_value = @return_value + ‘ ‘ + @EmpName
      FETCH NEXT FROM MyCursor INTO @EmpName
      END
      CLOSE MyCursor
      DEALLOCATE MyCursor

      RETURN @return_value
      END
      GO

      SELECT DeptId, dbo.fn_Get_Emp_List(DeptId) AS EmpName FROM Employee
      GROUP BY DeptId
      GO

      Let me Know your feedback

      Mahesh Nair

    • This is probably a little too late, but instead of using a cursor in your function you can simply say

      CREATE FUNCTION [dbo].[fn_Get_Emp_List]
      (
      @DeptId INT
      )
      RETURNS NVARCHAR(MAX)
      AS
      BEGIN
      DECLARE @EmpNames AS NVARCHAR(MAX)

      SELECT @EmpNames = COALESCE(@EmpNames + ‘, ‘,”) + EmpName
      FROM Employee
      WHERE DeptId = @DeptId
      ORDER BY EmpName

      RETURN @EmpNames
      END

  6. Hello!

    I just came across this issue once again as I had received a development environment whose dba was on leave. Unfortunately, it was a SQL 2K8 box and I did not have the pass-through, Windows authentication for my login. I also did not have the sa password.
    I used the following method:

    http://blogs.msdn.com/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx

    And it worked! I was able to get through to the server, add myself as a user and use the server normally after the changes were rolled back!

    These are such common issues that you can almost prepare a SQL Server Troubleshooting Cheat Sheet!

    Take care,
    Bye!

  7. You are a genius. I was about to go town with a baseball bat on my PC until I saw this blog entry. Thanks a lot for the info!

  8. This method doesn’t work with SQL Server 2008 R2. Anyone has got any idea which method can work with SQL server 2008 R2. It works file with SQL 2005.

  9. Hi all.

    How I can change the sa password without logon to the SQL-Server?
    I have forgott it and the mixed authentication is not active.
    So i had tried to use the single user mode but without a goal!

    Is it possible to change the sa password in the master database?

    I search for a while on the internet but a don’t found a answer!

    Please help me to reset my sa password.

    Thand you

  10. 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?

  11. kumar:I dont know the passwod of sql server management studio can any body help me out in finding the password of my management studio for sql server authentication

  12. hi pinal,
    I face a problem of Login failed again and again in sql server authentication for sql server r2 2008……..

  13. 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.

  14. Pingback: SQL SERVER – Weekly Series – Memory Lane – #041 | Journey to SQL Authority with Pinal Dave

  15. Thanks! I thought I’d be reinstalling SQL and re-attaching datasbases. This article saved me a bunch of time. Couldn’t have been easier!

  16. Hi,
    To change the SQL sa password from a command prompt:
    Start a command prompt by typing Start – Run – cmd
    Enter the following commands, pressing Enter after each line
    OSQL -S yourservername -E
    1> EXEC sp_password NULL, ‘yourpassword’, ‘sa’
    2> GO

    Where yourservername is the name of your server and yourpassword is the new sa account password. Type exit twice to return to the server desktop.

    This works in SQL 2005. You can try with versions above Sql 2005

  17. Pingback: SQL SERVER – Reset SA Password – SQL in Sixty Seconds #066 | Journey to SQL Authority with Pinal Dave

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