How to change sa password in SQL Server 2008 express ?

Question

Dear friends,

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.

Answer 1

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