There are many blog posts talking about, how to recover “SysAdmin” password in case if it’s lost OR the server is new to you and no idea that has own and access to it. In my case the second option J
In my opinion, it’s very easy to work on GUI instead of command line CMD 🙂 I used a SSMS.
Steps:
1. Stop the SQL server agent
2. Take the SQL server in single user mode, earlier version, it’s a bit hard
All programs –> SQL server 2012 –> Configuration Tools –> SQL server configuration manager
Stop the agent and take the SQL server into single user mode.
3. Reboot the SQL service
4. Launch the SSMS and don’t click the connect button, you need to cancel it and then click the “New query” window on top of the SSMS and then connect.
If you click the connect button the object explorer will take one connection, Single user mode will allow only one connection. Because of that we need to cancel and click the new query window.
Note: You need to be an OS administrator.
5. Create a new login and add as an admin
CREATE LOGIN XXX WITH PASSWORD=Complex password' SP_ADDSRVROLEMEMBER XXX,'SYSADMIN'
Hope this would help someone.
10 Comments
Mauricio
This is a great tip, but requires a service restart. If you have access to the server desktop/console, you can also give a try to PSTools. Use PSEXEC to authenticate as System and open SSMS, then create your new SA login.
http://technet.microsoft.com/en-us/sysinternals/bb897553.aspx
and
http://blogs.msdn.com/b/sqlserverfaq/archive/2012/12/18/how-to-login-to-sql-server-with-nt-authority-system-using-psexec-tool.aspx
(usually, System has admin rights, but for extra-secure installations, you will have to use the service account that runs the SQL Service. If even that is locked, then your method is the only one possible).
cya!
Muthukkumaran kaliyamoorthy
Thanks Mauricio!
Phil Melling
There are just a couple of issues with this approach: you’ve got to take down the SQL Service and disrupt operations, and the instance is in mixed mode authentication (which you won’t know if you’ve never accessed it before). I prefer the route of using sqlcmd to create a login for your Windows account and giving that sysadmin rights. For that you’ll need to log on to the server as a local admin (so you’ll probably need to make friends with the server admins) but at least you don’t have to take down the SQL Server service and everyone can carry on their business as usual.
Muthukkumaran kaliyamoorthy
@phil We need to put the SQL into single user mode, which will stop all the operations.
DSI
Did not work for me I tried it, did put the server in single user mode.
The problem is when you launch the New Query Window, it automatically tries to connect to the sql server instance, it requires logon credentials. I don’t think your solution is complete or something is missing, need more clarification about your outlined solution.
Muthukkumaran kaliyamoorthy
Hi @DSI
Yes, it will ask the credentials. First time you have to cancel the window and need to click the query window and then give the credentials.
Chuck
Unless things have changed since earlier versions, I think you forgot one step. You must be logged onto the server as a member of the local administrators group.
Muthukkumaran kaliyamoorthy
Thanks Chuck. Yes, We need to log-on as OS admin.
Alex Franky
You can easily recover password of user account, if you have the password of SA account or you can contact to administrator to reset the password but here is simple cmd method to recover SQL password.
More information for any Update click this link : How to Reset SQL Server Password Manually?
Jason
Unfortunately my SQL server failed to start in single user mode. I later managed to reset the lost sa password with a third-party tool – SQL Server Password Changer. It’s worth a try.