Um blog sobre nada

Um conjunto de inutilidades que podem vir a ser úteis

Locked out of SQL Server

Posted by Diego em Junho 28, 2012


What to do if you get locked out of SQL Server:

I was trying to transfer objects from a SQL Server instance to another using the “Transfer SQL Server Objects Task” and I selected to copy all the security options

clip_image002

When the package was running I got an error saying that I did not have permissions to insert the logins on my destination server. The thing is, the package first deletes all the logins before inserting the new ones (I believe that’s because the DropObjectsFirst option was selected). Result: I got locked out of SQL.

Solution:

If SQL Server is started in single-user mode, any user who has membership in the BUILTIN\Administrators group can connect to SQL Server as a SQL Server administrator. The user can connect regardless of whether the BUILTIN\Administrators group has been granted a server login that is provisioned in the SYSADMIN fixed server role. This behaviour is by design and intended to be used for data recovery scenarios.

In this scenario you are connected as a SQL Server Admin so you have full rights. Remember to give your AD user (which is the same one you are connected at the moment) sysadmin access. If you simply create your user, restart SQL and log in with it, you won’t have access to anything.

A more detailed explanation on the process here: http://technet.microsoft.com/en-us/library/dd207004.aspx

Here is a link on how to start SQL Server on Single User:

Sqlauthority: http://blog.sqlauthority.com/2009/02/10/sql-server-start-sql-server-instance-in-single-user-mode/

TechNet: http://technet.microsoft.com/en-us/library/ms188236.aspx

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s

 
%d bloggers like this: