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
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.
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: