What will happen if you lost all your administrator account by mistake ? As per the best practice you have disabled the sa login. Those who had login with sysadmin rights left the company or not able to remember their password. Now you do not have any login with sysadmin rights. How do you get the administrative rights back?
I had the same kind of scenario on a server which we use very rarely. Later we realized that we lost the administrative rights on that instance and we did not had any idea to get back the sysadmin rights. The only option was destroy the instance and rebuild it.But Microsoft thought about this scenario and kept an option to recover the sysadmin rights. Let us discuss this in detail through this post.
How it works
To solve this issue, we should know two sql server start up parameter
-f | Starts an instance of SQL Server with minimal configuration. This is useful if the setting of a configuration value (for example, over-committing memory) has prevented the server from starting. Starting SQL Server in minimal configuration mode places SQL Server in single-user mode. |
-m | Starts an instance of SQL Server in single-user mode. When you start an instance of SQL Server in single-user mode, only a single user can connect, and the CHECKPOINT process is not started. CHECKPOINT guarantees that completed transactions are regularly written from the disk cache to the database device. Typically, this option is used if you experience problems with system databases that should be repaired. Enables the sp_configure allow updates option. By default, allow updates is disabled. Starting SQL Server in single-user mode enables any member of the computer's local Administrators group to connect to the instance of SQL Server as a member of the sysadmin fixed server role. |
When we start SQL server in single user mode, it is very common that application which using this server might take the connection and you will not get the connection. To avoid this situation there is an option to give the application name along with the start up parameter. For example, -mSQLCMD will restrict the connection only from an application named as SQLCMD. To connect only from SQMS, -mMicrosoft SQL Server Management Studio - Query . Note that the application name is case sensitive.Any custom build application can change its name by tweaking the connection string(How to do it?)
Now we are going to connect the instance using windows authentication method . Make sure that your windows account is part of the local administrator group of the server on which SQL server resides. Follow the below steps :
- Stop the sql server instance : This can be done by using the command prompt , SQL server configuration manager or services.msc. To stop from the command prompt, open the command prompt window with administrative rights(right click on the cmd icon and select Run as administrator) and run the command net stop mssqlserver for default instance and for named instance net stop smsqlserver$myinst replace myinst with your instance name.
- To start the SQL server in single user mode, run the following command from command prompt. net start mssqlserver /mSQLCMD for named instance,net start mssqlserver$myinst /mSQLCMD .You can do the same using the SQL server configuration manager by editing the startup parameter as given below and start the service from configuration manager.
I personally prefer to do it from the command prompt,which gives more control. Make sure that you are mentioning the correct application name with proper case. if you give application name as sqlcmd, when you try to connect it from either from commad prompt or SSMS, it will throw an error :
Login failed for user 'Mydomain\loginname'. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)
The error itself is misleading. When i tried , I was getting this error and took some time to find out the issue.
- Now you can connect to the sql server from the command prompt and can create a login with sysadmin rights or can assign sysadmin rights to an existing login. To connect to the SQL server from the command prompt , for default instance SQLCMD -Smyservername for named instance SQLCMD -Smyservername\myinstancename. Then you can use the following command to create a new login and adding into sysadmin role.
USE MASTER
GO
CREATE login NewsysadminLogin WITH password='passwordtest123'
GO
sp_addsrvrolemember 'NewsysadminLogin','sysadmin'
Now the login NewsysadminLogin will have the sysadmin rights on the server . Stop the service and start it .If you have modified the startup parameter through the configuration manager , do not forget to remove the singer user switch (-m) from there.If you liked this post, do like my page on FaceBook
Hi I followed this post and got no errors, but when i reopen SSMS i cant see the new login i created or login with it ? any ideas
ReplyDeleteI do not have any clue..it worked well for me. Could you please check the server name/instance name in which you created the login and trying to connect
DeleteGreat Article
DeleteFinal Year Project Domains for CSE
Project Centers in Chennai
JavaScript Training in Chennai
JavaScript Training in Chennai
What if the current windows account you logged in with was Disable in SQL too? And also sa is Disable
ReplyDeleteThen I can not run SQLCMD and it gives me Login failed error
Great article! We can also use some external tools to reset lost sa password or unlock a disabled sa account. Check out this video tutorial: https://www.youtube.com/watch?v=GfI-bo5TDB0
ReplyDeletehttp://bantalsilikon01.blogspot.com/
ReplyDeletejual bantal jual bantal jual bantal jual bantal jual bantal jual bantal jual bantal jual bantal jual bantal
sangkar jual sangkar jual sangkar jual sangkar jual sangkar jual sangkar jual sangkar jual
anne
cd anne jelita brenda anne caca tasya ruth
bantal web bantal bantal silikon pembicara hellow
bumbu bumbu bumbu bumbu bumbu distro distro
You can recover SQL server password by running sqlcmd on the command prompt
ReplyDeleteThen run the following transact-sql command
EXEC sp_addsrvrolemember 'DOMAIN\Username', 'sysadmin';
GO
If the above solution fails then you take the help of SQL Password Recovery Tool: http://www.sqlmdfviewer.org/sql-password-recovery-freeware.html
ReplyDeleteThanks for posting this useful content, Good to know about new things here, Let me share this, . LINUX training in pune
ReplyDeleteI loved the way you discuss the topic great work thanks for the share, Let me share this, Hadoop training in pune
ReplyDeleteThe blog or and best that is extremely useful to keep I can share the ideas
of the future as this is really what I was looking for, I am very comfortable and pleased to come here. Thank you very much.
tanki online | 2048 game|
شركة نقل عفش
ReplyDeleteاهم شركات مكافحة حشرات بالخبر كذلك معرض اهم شركة مكافحة حشرات بالدمام والخبر والجبيل والخبر والاحساء والقطيف كذلك شركة رش حشرات بالدمام ومكافحة الحشرات بالخبر
شركة مكافحة حشرات بالدمام
شركة تنظيف خزانات بجدة الجوهرة من افضل شركات تنظيف الخزانات بجدة حيث ان تنظيف خزانات بجدة يحتاج الى مهارة فى كيفية غسيل وتنظيف الخزانات الكبيرة والصغيرة بجدة على ايدى متخصصين فى تنظيف الخزانات بجدة
شركة تنظيف خزانات بجدة
شركة كشف تسربات المياه بالدمام
شركة نقل عفش واثاث
شركة نقل عفش بالرياض وجدة والدمام والخبر والجبيل اولقطيف والاحساء والرياض وجدة ومكة المدينة المنورة والخرج والطائف وخميس مشيط وبجدة افضل شركة نقل عفش بجدة نعرضها مجموعة الفا لنقل العفش بمكة والخرج والقصيم والطائف وتبوك وخميس مشيط ونجران وجيزان وبريدة والمدينة المنورة وينبع افضل شركات نقل الاثاث بالجبيل والطائف وخميس مشيط وبريدة وعنيزو وابها ونجران المدينة وينبع تبوك والقصيم الخرج حفر الباطن والظهران
ReplyDeleteشركة نقل عفش بجدة
شركة نقل عفش بالمدينة المنورة
شركة نقل اثاث بالرياض
شركة نقل عفش بالدمام
شركة نقل عفش بالطائف
شركة نقل عفش بمكة
ReplyDeleteشركة نقل عفش بينبع
شركة نقل عفش بالخرج
شركة نقل عفش ببريدة
شركة نقل عفش بخميس مشيط
Thanks a lot Nelson! Worked like a charm!
ReplyDelete
ReplyDeleteشركة اصباغ الكويت شركات الدهانات الكويت
شراء اثاث مستعمل الكويت شركة شراء اثاث مستعمل الكويت
فني كهربائي منازل الكويت كهربائي منازل بالكويت
شركة مكافحة البق الكويت افضل شركة مكافحة البق الكويت
شركة مكافحة حشرات الكويت افضل شركة مكافحة الحشرات الكويت
نقل عفش مبارك الكبير شركة نقل عفش مبارك الكبير
Password security is increasingly becoming a major concern for people and businesses on a personal and professional level. Many people use Free Random Password Generator, because they can not remember their own password. Anyways Keeping your systems, devices and networks secure with strong passwords is an absolute must today.
ReplyDeleteI drink 2 litres of flavoured milk every day and 2 protein shakes each with 40g protein. war machines hack
ReplyDeleteI drink 2 litres of flavoured milk every day and 2 protein shakes each with 40g protein. war machines hack
ReplyDeletezombie gunship survival hack Ur editing is next level man. I hv also seen brawl stars by ea. But this is fantastic
ReplyDeletemini golf king hack I am a BIG FAN of getting up at 5AM. It's perfect for quiet time, CREATIVE time and connection time. I hope to make videos as GREAT as yours in the near future. (I just created my first 7 day EPIC LIFE challenge
ReplyDeleteThank you for this, definitely will try out some of these just to try getting a summer body for our August beach trip!
ReplyDeletewar machines hack
After a year, analysing all kinds of diets, finally understood that we should eat normal foods that are grown locally from our native home town... no packed or imported foods. This is the trick but nobody ill like this comment!! factory inc hack
ReplyDeleteit's working fine for me! Thank you for sharing this with us dominations hack
ReplyDeletei tried it, great.
ReplyDeleteseaport hack
Hey everyone I am so excited about this tutorial because it has helped me to I really appreciate thanks for sharing horse riding tales hack
ReplyDeleteAaditri Technology is a leading website design, web development company in Delhi, India we offer custom website development and all types of digital marketing services.
ReplyDeleteSMO Service in Delhi
Magento Development Company in Delhi
Best cms development code in india
Gym and Fitness equipment store in India - Buy best quality Exercise & Fitness equipment's online for lowest price at Ansonsports.com
ReplyDeletebuy dumbbell online in india
online sports and fitness shop in india
buy fitness equipments online
buy sports goods online
Book online best pandit for all kinds of pooja program, Graha Shanti, festivals pooja, and all your religious programs from ravindrashastri.com
ReplyDeleteBook Pandit Ji Online
Astrologer in Laxmi Nagar
Pandit ji for Satyanarayan Katha in Vaishali
Mua vé máy bay tại Aivivu, tham khảo
ReplyDeleteVe may bay di My
vé máy bay tết 2021 giá rẻ
giá vé máy bay đi toronto Canada
đặt vé máy bay đi Pháp giá rẻ
giá vé máy bay đi Anh
vé máy bay giá rẻ nhất
combo đà nẵng 3 ngày 2 đêm 2021
combo nha trang
trung tâm visa trung quốc phùng chí kiên
chi phí cách ly khách sạn
Thank you for your valuable and useful information through the blog. I am appreciating with the way you shared the relevant, precious, and perfect information. Furthermore, I would like to also keep some sound knowledge of SEO COMPANY IN DELHI . Trionfo IT Services is one of the leading Digital marketing and Seo agency. We are located in Delhi If you Have Need of SEO Service Then Visit our Website you will get complete information related to SEO Services.
ReplyDelete