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)

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


2) Logged in using Windows Authentication


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)