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 (https://blog.sqlauthority.com)

, ,
Previous Post
SQLAuthority News – Author Visit – Virtual Tech Days August 2009
Next Post
SQL SERVER – Get a List of Fixed Hard Drive and Free Space on Server

Related Posts

43 Comments. Leave new

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

    Reply
  • Vikram Pendse
    August 4, 2009 2:14 pm

    Nice ! I face such issues in past and most of the time remain clueless, will keep this info with me,between is it a good habit and safe to keep SA authentication?

    Reply
  • 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

    Reply
  • 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.

    Reply
  • Gangadhar Kotu
    August 4, 2009 4:22 pm

    Good one.. Really useful in the real-time environments.

    Reply
  • Nakul Vachhrajani
    August 4, 2009 11:22 pm

    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.

    Reply
  • 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

    Reply
    • 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

      Reply
    • 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

      Reply
  • Nakul Vachhrajani
    March 30, 2010 1:08 am

    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:
    https://blogs.msdn.microsoft.com/raulga/2007/07/13/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005/

    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!

    Reply
  • 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!

    Reply
  • Akira Suzuki
    June 10, 2010 6:37 pm

    Thanks a log!! You saved my day!!

    Reply
  • Jayesh Prajapati
    September 27, 2010 3:13 pm

    hi pinal,

    i like your toipcs.
    it’s informative.

    Regards,
    Jayesh Prajapati

    Reply
  • 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.

    Reply
  • Very Helpful Post…

    Reply
  • I cannot make a new data base . It is giving me a error 262
    plz hlp

    Reply
  • thnx this article saved me

    Reply
  • thanks…

    Reply
  • 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

    Reply
  • Thanks

    Reply
  • 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?

    Reply
  • 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

    Reply

Leave a Reply