Sometimes there might be a situation when DBA may forget ‘sa’ password and there is no other way to login to SQL Server, in that situation DBA may reset the ‘sa’ password or add another user with SA rights.
To do this, first Stop SQL Server and then start SQL Server with –m option in startup parameter to start SQL in single user mode.
Then login to Windows with Domain Administrator and use sqlcmd utility to connect to sql server. Once connected, write t-sql commands to reset ‘sa’ password or add another domain user with SA rights. After completion, stop SQL Server, remove –m option from startup parameters and restart sql server.
To reset sa password, use the following script.
C:\Users\Administrator>sqlcmd -E -Ssql2k8
1> alter login sa with password = 'password'
This procedure has been tested with SQL Server 2008, where server was already added to domain.