I have been asked a question by couple of friends and colleagues to reset or obtain the password of SA login in SQL Server.
It happens with lack of proper documentation or standards, that password of SA is forgotten. This is very big hectic if no windows user assigned to admin privileges, through which you can reset the sa password. If it’s not then earlier there were no much options and one had to move the database files to other disk and reinstall the SQL server and hell lot of stuff.. that required your time and patience.
Thanks to DAC, Dedicated Admin Connection, feature introduced in SQL Server 2008. It allows “Windows Admin User” of hosted server to start the SQL Server with single user mode (switch -m), which inherits the admin privileges to that user, through which one can reset the sa password and can grant access the administrative rights to other users.
NOTE: One should have EXTRA precautions while granting LOCAL Admin rights to any Windows login.
