Reset SA password for SQL box

My old box was changed to different domain and I could not use Windows authetication to log into the SQL server there as “sysadmin”. I forgot the SA user password as well. After some search I found the following solutions

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.

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.

I used the second option and was able to reset SA’s password.

Start SQL server in “single user” mode.

To start SQL Server in single user mode is very simple procedure as displayed below.

Go to SQL Server Configuration Manager and click on SQL Server 2005 Services. Click on desired SQL Server instance and right click go to properties. On the Advance table enter param ‘-m;‘ before existing params in Startup Parameters box.

clip_image001

Make sure that you entered semi-comma after -m. Once that is completed, restart SQL Server services to take this in effect. Once this is done, now you will be only able to connect SQL Server using sqlcmd.

Make sure to remove newly added params after required work is completed to restart it in multi user mode.

Now I was able to login to the SQL server with windows authentication.

I change the password for user SA.

Thanks to links from:

SQL SERVER – Forgot the Password of Username SA

SQL SERVER – Start SQL Server Instance in Single User Mode

Have Multiline Strings in VS

I frequently face situation where a string variable has a long length, so long that I have to scroll the screen left to right to see it.  What helps in this situation is to have breaks in that string so that the full string can be seen without having to scroll left-right.

Using “@” before the string’s value helps in this case.  For example,

string mysql = "select table1.col1,table1.col2,table1.col3, table2.column3, table2.col4, table2.col4 from table1 join table2 on table1.col1 = table2.col2"

can be written as:

string mysql = @"select table1.col1,table1.col2,table1.col3
, table2.column3, table2.col4, table2.col4
from table1 join table2
on table1.col1 = table2.col2"