Locked sa Account/Forgot admin password in SQL Server
Shit happens all the time in world of technology and one of them in world of SQL Server is admin account locked out/ forgotten password for admin account due to any stupid reason. Some of those reasons can be
All logins that are members of the sysadmin fixed server role have been removed by mistake.
All Windows Groups that are members of the sysadmin fixed server role have been removed by mistake.
The logins that are members of the sysadmin fixed server role are for individuals who have left the company or who are not available.
The sa account is disabled or no one knows the password.
No need to worry because we are going to guide you through step by step instructions to clear this mess. You ready? It is recommended that you take a cup of coffee :) ..just kidding. Lets start now.
First of all you do not need to re install any thing to handle such situation. You are already saved if you selected the option of Mix Mode Authentication while installing SQL Server i.e. you can use both SQL Authentication and Windows Authentication to login to server.
From the Start page, start SQL Server Management Studio. On the View menu, select Registered Servers. (If your server is not already registered, right-click Local Server Groups, point to Tasks, and then click Register Local Servers.)
In the Registered Servers area, right-click your server, and then click SQL Server Configuration Manager. This should ask for permission to run as administrator, and then open the Configuration Manager program.
In SQL Server Configuration Manager, in the left pane, select SQL Server Services. In the right-pane, find your instance of SQL Server. (The default instance of SQL Server includes (MSSQLSERVER) after the computer name. Named instances appear in upper case with the same name that they have in Registered Servers.) Right-click the instance of SQL Server, and then click Properties.
On the Startup Parameters tab, in the Specify a startup parameter box, type -m and then click Add. (That's a dash then lower case letter m.)
Click OK, and after the message to restart, right-click your server name, and then click Restart. Wait..Please make sure that you have changed the start up type to Manual for SQL Server Agent. If this is set to automatic then before you login, SQL server agent will start by logging in and then late on you will get errors that only single user can be logged into the server. So do not forget this step. You can do this by typing services.msc in command window and then changing the start up type to Manual by editing the properties of SQL Server Agent.
After SQL Server has restarted your server will be in single-user mode.
Right-click the icon for Management Studio.Select Run as administrator. (This will pass your administrator credentials to SSMS.)
Connect with Object Explorer using Windows Authentication (which includes your Administrator credentials). Expand Security, expand Logins, and double-click your own login. On the Server Roles page, select sysadmin, and then click OK.
Now you have got sysadmin rights for your login. You can either reset the password for sa account or any other admin account.
The following steps now change SQL Server back to multi-user mode. Close SSMS.
In SQL Server Configuration Manager, in the left pane, select SQL Server Services. In the right-pane, right-click the instance of SQL Server, and then click Properties.
On the Startup Parameters tab, in the Existing parameters box, select -m and then click Remove.
Right-click your server name, and then click Restart.
Congratulations!!! Your problem is solved. Now enjoy using sysadmin rights on SQL Server.
Please do let me know if you face any problem. I hope this blog will clear your mess of sa locked account/sa forgotten password.
blakekathryn
titsay
romaincrisis
snowghoul
andulkaphoto
pixelskylines
