| Author | Message |
MetalliMyers
Administrator
A Pompous Ass
Location: LV-426
Joined: 3/12/2009
Post Count: 1723
|
Date Posted: 5/4/2011 10:54:42 AM Subject: Recover sql server if sa account password is lost.
http://blogs.msdn.com/b/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx
Raul Garcia from Microsoft said:
- Open the Configuration Manager tool from the "SQL Server 2005| Configuration" menu
- Stop the SQL Server Instance you need to recover
- Navigate to the “Advanced” tab, and in the Properties text box add “;–m” to the end of the list in the “Startup parameters” option
- Click the “OK” button and restart the SQL Server Instance
NOTE: make sure there is no space between “;” and “-m”, the registry parameter parser is sensitive to such typos. You should see an entry in the SQL Server ERRORLOG file that says “SQL Server started in single-user mode.”
- After the SQL Server Instance starts in single-user mode, the Windows Administrator account is able to connect to SQL Server using the sqlcmd utility using Windows authentication. You can use Transact-SQL commands such as "sp_addsrvrolemember" to add an existing login (or a newly created one) to the sysadmin server role.
The following example adds the account "Buck" in the "CONTOSO" domain to the SQL Server "sysadmin" role:
EXEC sp_addsrvrolemember 'CONTOSO\Buck', 'sysadmin';
GO
- Once the sysadmin access has been recovered, remove the “;-m” from the startup parameters using the Configuration Manager and restart the SQL Server Instance
Post Edited on 5/4/2011 10:57:07 AM
|
