Join Us!

Spaces are allowed; punctuation is not allowed except for periods, hyphens, and underscores.
A valid e-mail address. All e-mails from the system will be sent to this address. The e-mail address is not made public and will only be used if you wish to receive a new password or wish to receive certain news or notifications by e-mail.
CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.

Login

Enter your Project Envision username.
Enter the password that accompanies your username.
Request new password

Add User to Sysadmin Role Without Having Permissions

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.

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

C:\>sqlservr.exe –m –c

Or below if you are using named instance

C:\>sqlservr.exe –m –c –s InstanceName

The –m switch specifies single user mode and –c indicates that we will be starting it as a normal program not a service.

After that you should see a bunch of messages for Sql Server startup and you should be able to connect using sqlcmd

If you are connection to default instance use that command

sqlcmd –S ComputerName

If you have named instance, use the following command:

sqlcmd –S ComputerName\InstanceName

There is a possibility that you will encounter the below error if you have logon trigger defined.

C:\Windows\system32>sqlcmd -S W7 
Msg 17892, Level 14, State 1, Server W7, Line 1
Logon failed for login 'PROJECTENVISION\Administrator' due to trigger execution.

If you see that error you have to change the command

C:\>sqlservr.exe –m –c

to

C:\>sqlservr.exe –m –c –f

-f switch specifies to start Sql Server with minimal configuration

Now from sqlcmd you can add login to sysadmin server group.

sp_addsrvrolemember 'LoginName', 'sysadmin' 
go
quit

Now go back to the cmd prompt where you started Sql Server and by pressing Ctrl+C stop it.

Start Sql Server Service from services.msc, your problem is resolved