MS SQL Server 2012 - DBA Articles

Recover - System Administrator Access of SQL Server

This article describes how you can regain access to the SQL Server Database Engine as a system administrator. A system administrator can lose access to an instance of SQL Server because of one of the following reasons:
    01. All logins that are members of the sysadmin fixed server role have been removed by mistake.
    02. All Windows Groups that are members of the sysadmin fixed server role have been removed by mistake.
    03. The logins that are members of the sysadmin fixed server role are for individuals who have left the company
           or who are not available.
    04. The sa account is disabled or no one knows the password.
One way in which you can regain access is to reinstall SQL Server and attach all the databases to the new instance. This solution is time-consuming; and, to recover the logins, it might require restoring the master database from a backup. If the backup of the master database is older, it might not have all the information. If the backup of the master database is more recent, it might have the same logins as the previous instance; therefore, administrators will still be locked out.
The following instructions describe the process for connecting to SQL Server 2012 running on Windows 8. Slight adjustments for earlier versions of SQL Server or Windows are provided. These instructions must be performed while logged in to Windows as a member of the local administrators group, and they assume that SQL Server Management Studio is installed on the computer.
Problem Scenario
The following PREMMSSQLSERVER named instance SQL Server logged by prem\premnathrk. This instance has three SQL Server Login Users.
    01. PREM\PREMNATH - Public SQL Server User.
    02. prem\premnathrk - System Admin SQL Server User.
    03. sa - System Admin SQL Server User but it is disable mode.

PREM\PREMNATH user has public user authentication only.

Initially, prem\premnathrk user has system admin user authentication.

By mistake, unchecked the sysadmin user authentication and click OK.

System occur error, while click sa or any login user, because prem\premnathrk does not System Administrator User.

Now, PREMMSSQLSERVER named instance does not have any System Administator expect sa Login User. I logged out and login again in the named instance SQL Server by prem\premnathrk Login User. System displays error, while I tried to enable sa Login User.

Solution of Problem
In Windows 8 Environment, to open SQL Server Configuration Manager, in the Search charm, under Apps, type SQLServerManager11.msc (for SQL Server 2012) or SQLServerManager10.msc for (for SQL Server 2008), and then press Enter Key.
In SQL Server Configuration Manager, in the left pane, click SQL Server Services.

In the details pane, right-click SQL Server (PREMMSSQLSERVER) and then click Properties.

Click Startup Parameters and type -m.

Click Add and then OK.

Right-click particular PREMMSSQLSERVER named instance and click Restart option to restart SQL Server Services.

After SQL Server has restarted your server will be in single-user mode. Make sure that that SQL Server Agent is not running. If started, it will take your only connection.
On the Windows 8 start screen, right-click the icon for SQL Server Management Studio. At the bottom of the screen, select Run as administrator. (This will pass your administrator credentials to SSMS.)

Your Comment To AUTHOR