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
-
1Did you enable mixed mode authentication (and choose an SA password) during the install? – Joe Stefanelli Sep 23 '10 at 18:27
-
i cannot remember and it's not allowing me to do anything – ktm Sep 23 '10 at 19:12
5 Answers
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)

- 2,636
- 1
- 21
- 17
-
5
-
1What 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
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 startsqlservr.exe -c -sEXPRESS -m -f
from console) - connect to DAC:
sqlcmd -E -A -S .\EXPRESS
or from SSMS useadmin:.\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

- 288,378
- 40
- 442
- 569
-
5I 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
-
5Make 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
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

- 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
-
This may help you to reset your sa password for SQL 2008 and 2012
EXEC sp_password NULL, 'yourpassword', 'sa'

- 62,132
- 37
- 328
- 418

- 159
- 3
- 9
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.

- 48,840
- 22
- 240
- 204