MSSQL Server 2008 - Reset sa password

In this week, we were implementing PoC environment for voice analytics solution and received VMware image from vendor. After hosting image in our environment, which obviously required server name, IP address and domain changes, they were unable to login into SQL Server 2008 database.

We need to do ‘sa’ password reset and followed steps below for the same. Hope this helps.

• From windows services, stop SQLServer(MSSQLServer) service

• Open SQL Server Configuration Manager ( Start -> All Programs -> Microsoft SQL Server 2008 R2 -> Configuration Tools -> SQL Server Configuration Manager)
• Select SQL Server Services -> SQL Server(MSSQLServer). Right click and select ‘Properties’
• Go to ‘Advanced’ tab and change startup parameters by prefixing -m; as below

• Click ‘Ok’ to save the changes
• Start ‘SQLServer(MSSQLServer)’ from windows services
• Open command prompt (Start -> Run -> cmd and press ‘Enter’)
• Type ‘sqlcmd’ to start SQL command window and create new user as below

create login lucky with password='BeHappy#3'
print 'User lucky is created'
sp_addsrvrolemember ‘lucky’, ‘sysadmin’
go

•  Stop SQLServer(MSSQLService) from windows services
• Remove ‘-m’ from startup parameters, click ‘Ok’ to apply the changes and start SQLServer(MSSQLService) service

• Open ‘SQL Server Management Studio’ and login using newly created account (username : lucky, password=’BeHappy#3′)
• Right-click login account ‘sa’ from ‘Security -> Login’ and select ‘Properties’

• Change the password