How to change sa password in SQL Server 2008 express?

Question

I have installed SQL Server 2008 express and logging in through windows authentication, it doesn't allow me to do anything. How do i change 'sa' password or gain full privilege in my local computers 2008 express ? I am using windows 7.

Is your user account an Administrator account? Also, see this link: http://ashrafur.wordpress.com/2008/01/04/set-sa-password-using-sql-server-management-studio/ — Fosco, Sep 23 '10 at 18:25
Did you enable mixed mode authentication (and choose an SA password) during the install? — Joe Stefanelli, Sep 23 '10 at 18:27

score 66 · Answer 1 · answered Nov 11 '12 at 16:56

66

If you want to change your 'sa' password with SQL Server Management Studio, here are the steps:

Login using Windows Authentication and ".\SQLExpress" as Server Name
Change server authentication mode - Right click on root, choose Properties, from Security tab select "SQL Server and Windows Authentication mode", click OK
Set sa password - Navigate to Security > Logins > sa, right click on it, choose Properties, from General tab set the Password (don't close the window)
Grant permission - Go to Status tab, make sure the Grant and Enabled radiobuttons are chosen, click OK
Restart SQLEXPRESS service from your local services (Window+R > services.msc)

answered Nov 11 '12 at 16:56

Ani

2,636
1
21
17

5

This only works if you have the rights to change sa password. – Aaron Aug 14 '13 at 18:24
1

What if I get "user has no credentials to change authentication mode" in step 2? – Henry Aloni Sep 17 '13 at 12:07
Thanks Worked Perfect, Just you need to be running Management Studio in Admin Mode – David Fawzy May 30 '16 at 13:16
Probably the only end-to-end complete answer for this topic on the internet! – Syed Waqas Feb 02 '18 at 12:22
This answer works, But sometime you need permission to change the sa password, To **Get Permission to Change sa Password** , check [this](https://stackoverflow.com/a/43231188/2218697) link. Hope helps. – Shaiju T Nov 20 '19 at 06:23

score 35 · Accepted Answer · answered Sep 23 '10 at 19:12

35

You need to follow the steps described in Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out and add your own Windows user as a member of sysadmin:

shutdown MSSQL$EXPRESS service (or whatever the name of your SQL Express service is)
start add the -m and -f startup parameters (or you can start sqlservr.exe -c -sEXPRESS -m -f from console)
connect to DAC: sqlcmd -E -A -S .\EXPRESS or from SSMS use admin:.\EXPRESS
run create login [machinename\username] from windows to create your Windows login in SQL
run sp_addsrvrolemember 'machinename\username', 'sysadmin'; to make urself sysadmin member
restart service w/o the -m -f

answered Sep 23 '10 at 19:12

Remus Rusanu

288,378
40
442
569

5

I had to start the sql service using a trace option to get it to work, and also shut down any service that might try to connect to SQL express (like the Ektron indexing service): `sqlservr.exe -c -sSQLEXPRESS -m -f -T 7806`, then I connected to SSMS just using `.\sqlexpress` for the server name – Dan Jan 13 '12 at 16:41
5

Make sure you run your SSMS in elevated mode otherwise you might not get the connection. – Rashack Nov 20 '13 at 09:51
@Rashack comment is a very important point, otherwise your connection will fail if you are not running as the administrator. – pdwalker Jan 23 '18 at 05:31

Rob Sedgwick · Answer 3 · 2018-02-25T21:19:47.583

6

I didn't know the existing sa password so this is what I did:

Open Services in Control Panel
Find the "SQL Server (SQLEXPRESS)" entry and select properties
Stop the service
Enter "-m" at the beginning of the "Start parameters" fields. If there are other parameters there already add a semi-colon after -m;
Start the service
Open a Command Prompt

Enter the command:

osql -S YourPcName\SQLEXPRESS -E

(change YourPcName to whatever your PC is called).

At the prompt type the following commands:

alter login sa enable
go
sp_password NULL,'new_password','sa'
go
quit

Stop the "SQL Server (SQLEXPRESS)" service
Remove the "-m" from the Start parameters field
Start the service

edited Feb 25 '18 at 21:19

answered Feb 10 '15 at 11:13

Rob Sedgwick

4,342
6
50
87

This worked for us, even when adding a user (as the accepted answer recommends) failed. We used sqlcmd. – moodboom Dec 20 '18 at 19:47
Sql Server Express 2008 does not start with `-m` parameter. – e-info128 Jan 24 '22 at 02:21

score 5 · Answer 4 · edited Oct 12 '18 at 12:15

5

This may help you to reset your sa password for SQL 2008 and 2012

EXEC sp_password NULL, 'yourpassword', 'sa'

edited Oct 12 '18 at 12:15

Ogglas

62,132
37
328
418

answered Jul 12 '16 at 12:54

Sachin Panchal

159
3
9

Worked perfectly if you know `sa` password – Ogglas Oct 12 '18 at 12:16

live-love · Answer 5 · 2015-07-01T18:03:25.743

This is what worked for me:

Close all Sql Server referencing apps.
Open Services in Control Panel.
Find the "SQL Server (SQLEXPRESS)" entry and select properties.
Stop the service (all Sql Server services).
Enter "-m" at the Start parameters" fields.
Start the service (click on Start button on General Tab).
Open a Command Prompt (right click, Run as administrator if needed).
Enter the command:

osql -S localhost\SQLEXPRESS -E

(or change localhost to whatever your PC is called).
At the prompt type the following commands:

CREATE LOGIN my_Login_here WITH PASSWORD = 'my_Password_here'

go

sp_addsrvrolemember 'my_Login_here', 'sysadmin'

go

quit
Stop the "SQL Server (SQLEXPRESS)" service.
Remove the "-m" from the Start parameters field (if still there).
Start the service.
In Management Studio, use the login and password you just created. This should give it admin permission.

How to change sa password in SQL Server 2008 express?

5 Answers5

Linked