-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?

oNare
  • 3,181
  • 2
  • 20
  • 35
hoF
  • 1
  • 1
    by the way, when I click on Connect Bottom,Error number 18470 appears. – hoF Aug 11 '15 at 13:56
  • what is the complete error message? – KASQLDBA Aug 11 '15 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: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18470&LinkId=20476

    BUTTONS:

    OK

     – hoF Aug 11 '15 at 14:37

2 Answers2

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

Hector
  • 1,032
  • 1
  • 8
  • 22
  • 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 '15 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 '15 at 15:00
  • 1
    @hoF you need to log in with a different account. Don't have one? http://dba.stackexchange.com/questions/49677/no-sa-password-no-sql-server-management-studio-no-os-authentication/49680#49680 – Aaron Bertrand Aug 11 '15 at 15:04
  • No,I don't have different account :( what I should do now! – hoF Aug 11 '15 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 '15 at 15:11
  • I think I had one.Before I disabled my instance I created on.A sql server login... – hoF Aug 11 '15 at 15:14
  • Did using the PSExec from MS help you launch into SSMS as NT Auth and allow you to make changes? If so, please mark answer so we can provide this information to other users when they search for this question. Thanks – Hector Aug 11 '15 at 15:45
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.

Rajiv Rajan
  • 133
  • 3