It’s obvious that I love PowerShell and SQL Server. Together, they allow SQL Server DBA’s to solve just about any problem. Most of my projects have come from questions posted in Reddit’s SQL Server subreddit, including my most recent project, Reset-SqlSaPassword, which allows you to regain syadmin access to your SQL Servers, most times, in less than 20 seconds.
The “sa” in this module name is more about sysadmin access, and not the actual SQL Server login “sa”. Using this module, you can easily regain access to local or remote SQL Servers by either resetting the sa password, adding sysadmin role to existing login, or adding a new login (SQL or Windows) and granting the login sysadmin privileges.
“That seems totally insecure”, you may think. But this script will not work unless you have Windows administrator access on the server or workstation running the SQL Server instance. I’m actually running the script as a Domain Admin in the demo video, though it will also work with local admin privileges.
Initially, when I started the project, I was modifying the Startup Parameters using Microsoft.SqlServer.SqlWmiManagement but I found a huge bug that meant my script would only work using Microsoft.SqlServer.SMO using Version=18.104.22.168. If at all possible, I’d like my scripts to work across all environments, and this was a show stopper. Then I considered modifying the service itself (failed miserably) and even the registry. Then I realized I could do this all safely from the command-line, which meant the script would have no lasting impact since no permanent startup parameters are modified.
If you need to regain access to your SQL Server, just download Reset-SqlSaPassword from Microsoft Script Center and execute. It works on every Windows platform I tested, even Win XP (laugh, but sadly, some orgs still use it) and Windows Server 2016 Tech Preview. It also works on SQL Server 2005-2016, both clustered and stand-alone instances. It relies heavily on WMI calls and .NET, and does not require SMO or any admin tools. If you’re having access issues caused by unopened ports/strict firewall access, just run the script locally.