42

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.

Rubyist
  • 6,486
  • 10
  • 51
  • 86
ktm
  • 6,025
  • 24
  • 69
  • 95
  • 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
  • 1
    Did 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 Answers5

66

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

  1. Login using Windows Authentication and ".\SQLExpress" as Server Name
  2. Change server authentication mode - Right click on root, choose Properties, from Security tab select "SQL Server and Windows Authentication mode", click OK Change server authentication mode

  3. 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) Set sa password

  4. Grant permission - Go to Status tab, make sure the Grant and Enabled radiobuttons are chosen, click OK Grant permission

  5. Restart SQLEXPRESS service from your local services (Window+R > services.msc)

Ani
  • 2,636
  • 1
  • 21
  • 17
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
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
6

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

  1. Open Services in Control Panel

  2. Find the "SQL Server (SQLEXPRESS)" entry and select properties

  3. Stop the service

  4. Enter "-m" at the beginning of the "Start parameters" fields. If there are other parameters there already add a semi-colon after -m;

  5. Start the service

  6. Open a Command Prompt

Enter the command:

osql -S YourPcName\SQLEXPRESS -E

(change YourPcName to whatever your PC is called).

  1. At the prompt type the following commands:
alter login sa enable
go
sp_password NULL,'new_password','sa'
go
quit
  1. Stop the "SQL Server (SQLEXPRESS)" service

  2. Remove the "-m" from the Start parameters field

  3. Start the service

Rob Sedgwick
  • 4,342
  • 6
  • 50
  • 87
5

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

EXEC sp_password NULL, 'yourpassword', 'sa'
Ogglas
  • 62,132
  • 37
  • 328
  • 418
Sachin Panchal
  • 159
  • 3
  • 9
3

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.

live-love
  • 48,840
  • 22
  • 240
  • 204