how to reset SA password

  • I have couple SQL servers that nobody knows the SA password and nobody has sys_admin right to reset it. There is no BUILTIN either. How could I reset the SA password?

  • As far as I know, you need at least, to be Local Admin or part of Windows Admin Group on the machine running SQL.

    You need to stop the instance, and start MS-SQL on single user mode. Once you start on single user mode, connect using sqlcmd and add yourself or whoever needs it, to the sysadmin server role, this way:

    EXEC sp_addsrvrolemember 'CONTOSO\Joe', 'sysadmin';

    GO

    Now, all those actions are really intrusive: stop sql, star sql, override permissions, etc. So be sure you have permissions for that. Any attempt to do this without proper authorization or informing your bosses or right department, may cause you serious problems, in some cases, loss your job.

  • I have tried this. The problem is the user I login with (my domain account) when I use command line does not have right to grant sys_admin right. I am already in the administrator group on the server. I think the problem is the BUILTIN is not in SQL.

  • will75 (4/30/2013)


    I have tried this. The problem is the user I login with (my domain account) when I use command line does not have right to grant sys_admin right. I am already in the administrator group on the server. I think the problem is the BUILTIN is not in SQL.

    If you are part of Windows Admin Group on that machine, and you start MS-SQL on single user mode, you should be able to reset sa:

    Disaster Recovery: What to do when the SA account password is lost in SQL Server 2005:

    http://blogs.msdn.com/b/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx

    Now, above process is for SQL2005, but I do believe still applies for SQL2008.

  • You have followed the information in the following article: http://msdn.microsoft.com/en-us/library/ms188236(v=sql.100).aspx?

  • There are some password reset utilities out there - Advanced Password Recovery - which can reset things. Not sure the costs - but worth a look as a last resort.

    ________________________________________________________________________________________________
    Regards
    Steve
    SQL 2008 DBA/DBD - MCTS/MCITP

    Please don't trust me, test the solutions I give you before using them.

  • You always can login using windows authentication and reset the password in SQL management studio. if your SQL installs are installed in mixed mode with both SQL and Windows authentication.

    ________________________________________________________________________________________________
    Regards
    Steve
    SQL 2008 DBA/DBD - MCTS/MCITP

    Please don't trust me, test the solutions I give you before using them.

  • some links i have saved:

    adding a sysadmin back toa server:

    http://beyondrelational.com/modules/2/blogs/115/posts/11143/how-to-access-a-newly-installed-sql-server-2008-r2-instance-if-you-do-not-know-the-login-and-passwor.aspx

    http://sev17.com/2011/10/22/gaining-sql-server-sysadmin-access/

    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!

  • If you already in administrator group then just login using windows authentication, Create new user with admin rights and login through that login.

    Using new login u also can chaneg password of already existing SA user.

  • Ask you SA (system administrator) to reset the password.

    Thanks.

  • i found this bat file from microsoft which is pretty much bullet proof:

    it adds the current user as an administrator to the sql server that is prompted in the command line text.

    this was a million times easier than getting the isntance in single user mode and all the other stuff involved in the original lik i provided.

    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!

  • An SA password of MS SQL Server can be reset by using Windows Authentication. If Builtin/Administrator is present in the SQL Server, you can login with an ID which is member of Administrators group and reset SA password in SQL Server. Just follow the steps shown below:

    Step 1: Log in to SQL server by using Windows Authentication.

    Step 2: In Object Explorer, open Security folder > Logins folder.

    Step 3: Right click on SA account and go to Properties.

    Step 4: Type a new SQL SA password, and confirm it. Click OK to finish.

    But if Basic Authentication[/i] is selected during SQL Server installation instead of Windows Authentication, then an SQL user cannot be able to follows the steps shown above. In such situation one should check some third-party SQL Password Recovery tools. I would recommend here SysInfoTools MS SQL Server Password Recovery here to reset a new password for SA login to unlock SQL Server. It allows users to reset password for both logins, individual as well as SA login.

    Thanks.

  • "Basic Authentication"? Do you mean "SQL Server Authentication"?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Don't worry, you can still regain your SA password in SQL Server with little bit of technical help and the manual solution which i am going to provide, so you can solve your problem without much confusion.

    Here is how you can do it with much ease and I am quite sure that this will be a helpful information for you. Visit this post to know the steps: recover SA password[/url] 🙂

  • Change SQL Server Password Using SQL Script

    Try this :-

    Open the SQL Server Management Studio.

    Open a New Query.

    Copy, paste, and execute the following:

    GO

    ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]

    GO

    USE [master]

    GO

    ALTER LOGIN [sa] WITH PASSWORD=N'NewPassword' MUST_CHANGE

    GO

    where NewPassword is the password you wish to use for the sa account.

    SSMS Expert

Viewing 15 posts - 1 through 15 (of 16 total)

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