-1

I am new in SQL Server. Wrongly I disabled my instance when I'd worked with Logins. Now I can not connect to Database Engine.

Could you please help me?

3
  • 1
    by the way, when I click on Connect Bottom,Error number 18470 appears.
    – hoF
    Aug 11, 2015 at 13:56
  • what is the complete error message?
    – KASQLDBA
    Aug 11, 2015 at 14:09
  • TITLE: Connect to Server ------------------------------ Cannot connect to .. ------------------------------ ADDITIONAL INFORMATION: Login failed for user 'WIN-5F33ESG08MR\asanetlearn'. Reason: The account is disabled. (Microsoft SQL Server, Error: 18470) For help, click: go.microsoft.com/… ------------------------------ BUTTONS: OK ------------------------------
    – hoF
    Aug 11, 2015 at 14:37

2 Answers 2

1

Download PSExec from Microsoft, then use it to launch Management Studio once you have it installed: PsExec -s -i "C:...\Ssms.exe"

This will connect as NT AUTHORITY\SYSTEM and will allow you to do things in Object Explore such as reset SA password. Thanks to user Aaron Bertrand for the information...

7
  • As I told,I can not connect to my sql instance to do this kind of queries. As soon as i click connect Database Engine I a prevented by an error
    – hoF
    Aug 11, 2015 at 14:38
  • Correct, thats why I stated this will need to be done with another user login such as SA or windows AUTH if you enabled that when creating the instance. Let us know if you cannot use the above method. Someone here may know of a workaround to that temporarily.
    – Hector
    Aug 11, 2015 at 15:00
  • 1
    @hoF you need to log in with a different account. Don't have one? dba.stackexchange.com/questions/49677/… Aug 11, 2015 at 15:04
  • No,I don't have different account :( what I should do now!
    – hoF
    Aug 11, 2015 at 15:09
  • @hoF Aaron Bertrand provided you a link to read. Specifically check out the part where it discusses the folliwng ..... "Essentially, you download PSExec from Microsoft, then use it to launch Management Studio once you have it installed: PsExec -s -i "C:\...\Ssms.exe" This will connect as NT AUTHORITY\SYSTEM and will allow you to do things in Object Explorer, like:...." Once you get that far, you can change the password to SA etc.... Please read content that we are providing you and take action before being quick to respond. It is the only way this forum will be of any help to you...
    – Hector
    Aug 11, 2015 at 15:11
0

ok looks like you have locked out your SQL login.

Hit the Windows key to bring up the Start menu along with the Search bar. Type "services.msc" in the Search bar. This will launch the Services MMC.

Now find your SQL Server and SQL Server Agent services and stop both of them. Right click the SQL Server service > Properties > Startup and add "-m" in the "Specify a startup parameter" box (without the double quotes). This will start it in single user mode. Now start the SQL Server service.

Next, bring up a command prompt and run this :

  • sqlcmd -S your_server_name -E

The "-E" parameter is used to start in Windows Authentication. Now use this syntax to create a new powerful account :

  • create login POWERFUL_USER with password='Change@1000'
  • go
  • exec sp_addsrvrolemember [POWERFUL_USER], [sysadmin]
  • go
  • exit

This will now exit you from the command window. You can now connect to SQL Server (with SQL Authentication) by using the POWERFUL_USER username and password.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.