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

SQL SERVER – Find Hostname and Current Logged In User Name

I work in an environment wherein I connect to multiple servers across the world. Time and again, my SSMS is connected to a myriad of servers that kindles a lot of confusion. I frequently use the following trick to separate different connections, which I mentioned in my blog sometime back SQL SERVER – 2008 – Change Color of Status Bar of SSMS Query Editor. However, this trick does not help when a huge number of different connections are open. In such a case, I use the following handy script. Do not go by the length of the script; it might be very short but always works great!

Now, let’s take a look at the execution of this script in two different scenarios.

1) Logged in using SQL Authentication

SELECT HOST_NAME() AS HostName, SUSER_NAME() LoggedInUser

2) Logged in using Windows Authentication

SELECT HOST_NAME() AS HostName, SUSER_NAME() LoggedInUser

It is quite evident from both the above cases that we get correct logged-in username and hostname. Let me know if this script is helpful to you when you face a similar situation.

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