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)
pinaldave
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