Unableto Login from windows\SQL Authentication Both, Admin Locked out

  • Hello,

    Some application installed sql server 2008 Standard Edition on windows 2008 Server. The person who installed that application is left. We don't know what is SA account password. SQL Server is running under "Local system" Account. I am a local domain admin on that Box. But I can not connect to that instance of SQL server due to login failed error. I tried to change SQL Server Service account to "Local Service" AND "Network Service" but still can not connect.

    Can someone tell me what to do now?

  • Thanks but for that you should be able to connect to SQL server using windows authentication too. And My question is I can not connect using windows authentication.

    I already tried "sqlcmd -S."

  • this is my go-to script for this issue:

    Login to the server itself, make sure you have local admin rights on the operating system side of things.

    Now simply download and run this script from an administrative command prompt window:

    https://gist.github.com/wadewegner/1677788

    you are prompted for which instance(blank fro the default instance, otherwise put in the name)

    it adds you, the current logged in windows user, as a sysadmin to the SQ Linstance. it stops and starts the service, so mission critical servers should wait until a maintenance window when you can bounce the service, but it's a fast process.

    repeat for each SQL instance on the machine you need to take over.

    I've used this on nearly every server I have, in a recent takeover; network guys gave me local admin to a box, and i took over the SQL side of things.

    the original copy of this script, direct from microsoft was here, but has been moved because they retired the archive. I found a copy by searching for "addselftosqlsysadmin"

    http://archive.msdn.microsoft.com/Project/Download/FileDownload.aspx?ProjectName=addselftosqlsysadmin&DownloadId=9198

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Hello Sorry but did not work for me, here is what I got,

    Adding 'domain\user' to the 'sysadmin' role on SQL Server instance 'SQLEXPRESS'.

    Verify the 'MSSQL$SQLEXPRESS' service exists ...

    [SC] EnumQueryServicesStatus:OpenService FAILED 1060:

    The specified service does not exist as an installed service.

    'MSSQL$SQLEXPRESS' service is invalid

    'domain\user' was NOT added to the 'sysadmin' role: SQL return code is N/A.

    I have SQL Server 2008 Standard Edition and its a named instance. It will still work?

    Where do I have to find and replace?

  • no find and replace required;

    check the name of the instance, and also make sure the instance is running in services.

    for example, if i go to the services applet in administrative tools, on my dev machine i see the names here:

    i have four different instances.

    to take over the default MSSQLSERVER, i type MSSQLSERVER

    to take over any of the others, i type only the name of the instance, ie "SQL2012", and the script should work.

    I don't have a SQLEXPRESS instance, for example, so i'd get the same error as you if i left it blank.

    to take over this server, i'd have to run the script four times, once for each of these:

    MSSQLSERVER

    SQL2005

    SQL2008R2

    SQL2012

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks again But it did not prompted me to specify my instance name.

    I dont have any default instance but I have only one named instance. Thats what I can see in Services.msc.

    So it does not prompt me to do specify instance name. and gave me this error message. How do I resolve that?

  • i suspect that's not the exact sameversion then, my apologies. i assumed it was the same.

    this is the exact version i am using; does this help?

    addselftosqlsysadmin.zip

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Okay Thanks a lot. I will try that soon and let u know.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply