Add User to Sysadmin Role Without Having Permissions on SQL Server

To totally unlock this section you need to Log-in

Login

Adding user to server role is very simple task if SQL Server is working correctly, but what should we do if it was misconfigured by us or someone else. In such way that our login now longer works, sa login is disabled and we do not have any other login which would allow connect to SQL Server. This is situation is bad but it is not dramatic, we can use simple procedure outlined below to resolve the problem.

The presented approach is based on the fact that our user (Windows user no matter if we are using AD or not) have administrator rights on Windows. Hence it implies that I will have full rights to SQL Server, when SQL Server will work in single user mode.

The first step is to logon locally to machine, on which SQL Server is installed as a member of the local Administrators group.

[tweet]

Next stop desired SQL Server service, be aware that if SQL Server Agent is running you should also stop that service, otherwise SQL Server Agent will use the connection when you will start SQL Server in Single User Mode.

Next we start SQL Server in single user admin mode and not as service, from the elevated command line prompt. You can do it by using below command if you are using default instance:

To totally unlock this section you need to Log-in

Login