How can a lost SA password be reset on SQL Server
Posted on 2014-12-17
I've been handed a SQL Server 2008 to maintain. The previous admin has "lost" the SA password. No other accounts on the database server have SA privilages. I want to gain SA access to the database server. I've read but never done the following:
1. Stop the sql server services.
2. Start the sql server instance in single-user mode: SQLServr.exe -m
3. Connect to the instance: SQLCMD -S <Server_Name\Instance_Name>
4. Create a login: 1> CREATE LOGIN 'Login_Name>' with PASSWORD='<Password>'
5. Give the user SA rights: SP_ADDSRVROLEMEMBER '<Login_Name>','SYSADMIN'
6. Stop the sql server services.
7. Restart normally.
Is this the correct procedure? Any suggestions?
There is one production database on this server. The transaction log is about 36GM and the database is about 600MB.
I will tackle that once I gain SA rights.