If the unthinkable happens and you forget your SQL Server password, there are ways to get back into the system.
You’re a proud and a trusted DBA at your organization. You’re responsible for maintaining and updating several important services running on SQL Servers in your production environment. You’ve decided to perform the following steps—which are the kind of best practices any solid DBA would take—to secure your company’s SQL Servers from any unauthorized access:
Because you’ve set the SA password to be so complex—and you’ve been using your domain account and not the SA account for all your daily database-related activity on SQL Server—the unthinkable has happened. You forgot your SQL Server SA password.
You were the only person who knew the SA password in your team. Now you don’t remember what it was and you need to make some server-level configuration changes to your production SQL Server boxes. What are you going to do now? Here are some options:
Assume all your attempts to log in to the system using the SA password have failed. Now it’s time for you to call for reinforcements: the Microsoft product support services team. Here’s what they might tell you to do:
This is a backdoor to SQL Server 2012 that will help you gain SYSADMIN access to your production SQL Servers. However, this means your Windows account will have to be a member of the local administrators group on Windows Servers where SQL Server 2012 services are running.
SQL Server lets any member of a Local Administrators group connect to SQL Server with SYSADMIN privileges.
Here are the steps to take control of your SQL Server 2012 as an SA:
SQLCMD –S <Server_Name\Instance_Name>
Example: SQLCMD –S "SALEEMHAKANI"
1> CREATE LOGIN '<Login_Name>' with PASSWORD='<Password>'
1> CREATE LOGIN SQL_SALEEM WITH PASSWORD='$@L649$@m'
1> SP_ADDSRVROLEMEMBER '<Login_Name>','SYSADMIN'
1> SP_ADDSRVROLEMEMBER '<LOGIN_NAME>','SYSADMIN'
1> SP_ADDSRVROLEMEMBER SQL_SALEEM,'SYSADMIN'
Saleem Hakani is a principal architect at Microsoft with more than 18 years of experience. He has worked with SQL Server since 1992 and has worked on numerous large-scale Microsoft services as an engineer and architect over the last seven years, including Hotmail, Bing and MSN. Hakani leads the worldwide SQL Server Community for Microsoft employees, and is a technical presenter for various Microsoft events including TechReady, SQLFEST, SQL-SCHOOL and SQLPASS.