Windows How to Connect to SQL Server When System Administrators Are Locked Out?

How to Connect to SQL Server When System Administrators Are Locked Out?

Connect to SQL Server When System Administrators Are Locked Out

In this topic, we are telling you the How you can regain or recover the access to the SQL Server Database as a System Administrator.

Connect to SQL Server When System Administrators Are Locked Out

Firstly, I am telling you the reasons for how an administrator loses access to SQL Server.

All the login of members of the sysadmin or sub-admin fixed the server role & been removed by a mistake.
All the groups of windows that are the members of the sub-admin or sysadmin fixed the server & role been removed by the mistake.
The SA account is deactivated & No one remembers the password.

There is one way available in which you can regain access to reinstall SQL Server & Join all the databases to the Fresh Instance. But this solution is time-consuming also for recovering all the logins.

It might be restoring the master database from the backup.

Solution

The First Step is to start the SQL Server management studio. Click on the View menu, and select Registered Users. (If any case that your server is not registered already, then right-click on local server groups, point your mouse to Tasks, & then click on Register local Servers.)
After clicking on Register Local Servers area, Right Click on your server, then click on SQL Server Configuration Manager. After click it will ask you to run as administrator, then Open the Configuration Manager Program.
Then close management studio
In the SQL Server Configure manager, on the left side select SQL Server services. then on the right side search your Instance SQL Server. (There is also a default instance of SQL Server which includes MSSQLSERVER) just after the computer name. and Named instances appear in the Upper case. With the same name that they have in the registered servers. Press the right-click button on the instance of SQL Server, and then click on Properties.
Then on the startup parameters tab, there is specify startup parameter box, type -m and the click on Add.
Click on OK, then message to restart, after that right-click on your server name & click on the Restart Button.
When the SEL server has restarted yo server it will be in the single-user mode. be make sure that the SQL server agent is stopped if started then it will take your connection only.
Windows8 start screen, Right-click on the icon of Management studio, which is at the bottom of the screen, then select run as administrator.

  • Connect with the object explorer while using window authentication. Expand the Security, expand logins also, and then Click your login. On the age of Server roles, select the sysadmin, and then click OK
  • Another option, instead of Connect with the Object explorer, connects with Query window using windows authentication (Which also included the windows credentials). Execute the code such as to add a new windows authentication login which is a member of the sysadmin. fixed server role.
  • After execute, If your SQL Server is run in the mixed authentication mode, connect with Query window using windows authentication (Which also included the windows credentials). Execute the code such as to create a new windows authentication login which is a member of the sysadmin. fixed server role.
  • After execute, If your SQL Server is run in the mixed authentication mode, and you want to reset the credential of the SA a/c, connect with Query window using windows authentication (Which also included the windows credentials). Change the credential of SA a/c with the following syntax.

The above steps now change the SQL Server back to multi-user mode. Close SSMS
In the SQL Server Config manager, then on the left side(Pane), select the SQL Server Services, then on the right side(Pane) press the right-click button on the instances of SQL Server, then click on properties.
On the Startup Parameters tab – inside the Existing parameters box, Select the (-m) and then click on Remove.
Press Right-click on your server name, and then click on Restart.

How to fix the problem of Connecting to SQL Server When System Administrators Are Locked Out/ Error on unable to connect to SQL Server When System Administrators Are Locked Out

Leave a Reply

Related Post