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)
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.
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?
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
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.
Good one.. Really useful in the real-time environments.
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.
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
Thank jdobbins…
It was late but no prob… a knowledge update…
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!
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!
Thanks a log!! You saved my day!!
hi pinal,
i like your toipcs.
it’s informative.
Regards,
Jayesh Prajapati
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.
Very Helpful Post…
I cannot make a new data base . It is giving me a error 262
plz hlp
thnx this article saved me
thanks…
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
Thanks
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?
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