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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

This may help you to reset your sa password for SQL 2008 and 2012  EXEC sp password NULL   yourpassword    sa

User · Answer

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

[sql-server] How to change sa password in SQL Server 2008 express?

Examples related to sql-server

Examples related to sql-server-2008

Examples related to windows-7