Dołącz do nas!

Wszystkie znaki interpunkcyjne za wyjątkiem kropek, łączników, znaków podkreśleń i spacji są niedozwolone.
Adres e-mail, koniecznie prawidłowy. Będą nań przesyłane wszystkie wiadomości e-mail od systemu. Adres nie jest udostępniany publicznie i będzie wykorzystywany jedynie w wypadku prośby o przesłanie nowego hasła lub do przesyłania informacji o nowościach czy innych komunikatów.
CAPTCHA
To pytanie ma na celu ochronę przed automatycznym spamowaniem oraz ustalenie, czy odwiedzający stronę jest człowiekiem.
Image CAPTCHA
Wpisz kod widoczny powyżej.

Logowanie

Nazwa użytkownika witryny Project Envision.
Hasło powiązane z nazwą użytkownika.
Prześlij nowe hasło

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