Home / server / Questions / 200128
Accepted
Jason
Asked: 2010-11-10 18:20:54 +0800 CST

Forgot MSSQL sa Password

  • 772

Someone else setup my laptop with SQLServer. My windows account is a user but is not a sysadmin.

I don't know the sa password and I have no way of finding it.

How can I reset the sa password or make my account (which is an admin on the machine) a sql sysadmin?

Do I need to reinstall to fix this?

sql-server

2 Answers

  1. Same answer as here:

    Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out

    Resolution

    Start the instance of SQL Server in single-user mode by using either the -m or -f options. Any member of the computer's local Administrators group can then connect to the instance of SQL Server as a member of the sysadmin fixed server role.

    Once you're in via the admin connection (admin:localhost from SSMS, or -E -A from sqlcmd), add yourself to the sysadmin group:

    CREATE LOGIN [domain\yourname] FROM WINDOWS;
EXEC sp_addsrvrolemember 'domain\yourname', 'sysadmin';

    or reset the sa password:

    ALTER LOGIN [sa] WITH PASSWORD = '...';

    then restart the server in normal mode.

    • 5
  2. Best Answer

    I will answer this for both MS SQL as well as My SQL (just to make sure I am covering both basis)

    Microsoft SQL

    1.Open a command prompt window by selecting "Start"-->"Run...", and typing "cmd.exe" in the "Run" dialog box.

    2.Change to the directory in which the MSSQL or MSDE utilities are stored (this is usually C:MSDEbinn, C:Program FilesMSSQLbinn, etc.).

    3.Type the following command where is the password you have chosen:

    osql -U sa -P "" -Q "sp_password NULL,<newpassword>,sa"

    MySQL

    First stop mysql from running ( service mysqld stop ) Then run mysql safe . . .

    mysqld_safe --skip-grant-tables

    then

    mysql --user=root mysql

    then use the following command:

    update user set Password=PASSWORD('new-password') where user='root'; flush privileges; exit;

    and finally restart mysql again

    Hope that helps no matter which DB system your using :-)

    • 3