Today a friend was complaining about how he had forgotten the sa password for his SQL Server…. Oh Dear.
If you follow Microsoft’s best practices for SQL security, as my friend did, you will have also disabled the BUILT-INAdministrators login… Oh Dear Oh Dear.
Fear not, we will have you back in within minutes, just follow these instructions:
- Logon to Windows on the SQL server a system administrator.
- Open CMD and type the following command to stop the SQL service
NET STOP MSSQL, Replacing MSSQL with your server instance name, for example: MSSQL$Instance.
NET START MSSQL /mto start SQL up in Single User Mode.
- Open SQL Management Studio, and connect to your server using windows authentication.
- Open a New Query window and type the following into the query window:
CREATE LOGIN <username>
SP_ADDSRVROLEMEMBER '<username>', 'sysadmin'
CREATE LOGIN m0rph3us
SP_ADDSRVROLEMEMBER 'm0rph3us', 'sysadmin'
- Click Execute. This will create the required username, with the desired password, and add that username to the sysadmin role, granting full control of the server.
- Close SQL Management Studio.
- Open CMD and type
NET STOP MSSQL.
NET START MSSQL.
- Open SQL Management Studio, and connect to the server using SQL Authentication, and input the username and password we created earlier.
You should now have regained full control of your SQL server, so go ahead and change the sa password.
I know these steps definately work in Microsoft SQL Server 2005, but I have not tried it on any other version of SQL. Please let me know in the comments if you try this method in another version of SQL, and the outcome and I will updater this post.