Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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 :-)

share|improve this answer
1  
I think you forgot the command on the MSSQL one. –  Blankasaurus Nov 10 '10 at 2:49
    
thanks - think the code box disappeared on adding... –  Glenn Kelley Nov 10 '10 at 3:56
1  
Out of curiosity: why does the osql command let you reset SA's password if you are not member of the sysadmin role? He explicitly stated his Windows user account doesn't have the rights to do so. –  Massimo Jun 9 '11 at 7:50

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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