Nearly a decade ago I posted a blog about how to get into SQL Server if you find yourself locked out. It remains one of the most viewed posts on the site because it’s a common issue. I strike it at least a couple of times a month where we have a client who wants us to assess their SQL environment, but they actually don’t know who can provide sysadmin access.

Back in 2013 I listed a bunch of ways to get into SQL if you didn’t have a SQL Sysadmin credential to use or access to someone who did, but I wanted to revisit it today because that’s not my go to anymore. A lot has changed in the last 9 years, and one of the most impactful has been the awesome community tool DBATools. You can check it out at DBATools.io. Honestly this set of powershell scripts streamlines so many of the processes I used to do manually and SQL access is one of these.

Using the Reset-DbaAdmin command you can do all the manual steps outlined in the post above in a line of powershell. You should be warned you are still not some super untraceable hacker – the command restarts SQL in the same way the manual method does, but it does it all in one line of code, handles a bunch of the more common issues with that method(such as someone else stealing your connection) and it does it all SUPER FAST.

So if you have just started a job and there’s a critical server that no-one knows the credentials for, or the password vault has a set of invalid credentials, or whatever reason is preventing you logging in as sysadmin – check out DBATools for a quick and easy way of getting back into your server – and as an added bonus you will now have access to one of the best free additions to your DBA toolbelt.

I was working on some Powershell automation for a weekly process for one of my colleagues and set up the powershell script to run via a SQL 2014 agent job.  After some initial permissions errors I ran into this error:

Executed as user: <Agent Account>. A job step received an error at line 3 in a PowerShell script. The corresponding line is ‘$space.ForegroundColor = $host.ui.rawui.ForegroundColor ‘. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘Exception setting “ForegroundColor”: “Cannot convert null to type “System.ConsoleColor” due to enumeration values that are not valid. Specify one of the following enumeration values and try again. The possible enumeration values are “Black,DarkBlue,DarkGreen,DarkCyan,DarkRed, DarkMagenta,DarkYellow,Gray,DarkGray,Blue,Green,Cyan,Red,Magenta,Yellow,White”.”  ‘.  Process Exit Code -1.  The step failed.

  This was really confusing at first because there was certainly no code that manipulated the console output – I was running from an agent job.  After a little testing I found the error to be with a piece of code within my script which DOES manipulate the console output – specifically the native CLS function.  As that was obviously part of the process of me testing the code and not anything I was going to need for the piece of automation I was working on removing the “CLS” from my script resolved the problem.

The error message was really misleading here, and I hope that it is addressed by Microsoft in the future.  It references a specific line of code in an agent job step, but for me that line corresponded with an echo statement.  At the least it should indicate that the error is in a called piece of code, not in the source script, and preferably shouldn’t generate an error at all, as all the output from the job step is in plain text.