Couple of weeks ago I was analyzing a server for space and noticed SSISDB database was abnormally huge (this Instance was running just a handful of packages). I noticed couple of internal schema tables in SSISDB were huge (with some hundreds of millions of rows), well that’s not right. There should be SSIS Server maintenance job which SQL server creates to purge older entries based on the retention settings right? My immediate action was to check the retention period set and what’s the status of the job. As I suspected, the job was failing (looks like this has been failing since ages) with below error.
The job failed. The Job was invoked by Schedule 9 (SSISDB Scheduler). The last step to run was step 1 (SSIS Server Operation Records Maintenance).
Execute as Login failed for the requested login ‘##MS_SSISServerCleanupJobLogin##’
Next step is to investigate whats up with the user account which the job is complaining about and I noticed ‘##MS_SSISServerCleanupJobUser##’ is orphaned in SSIS database as shown below.
Compare with how it looks on an Instance where this Job is running successfully.
Well, with this the fix is pretty evident, Map the poor fella to the corresponding login (Syntax shown below).
Once this has been done, I kicked off the job and it ran successfully (It took almost 2 hours to finish – had to purge few millions of records) and the subsequent scheduled runs were quick (Just few seconds as expected)
In this blog post, let’s see how to regain admin access on a SQL Server Instance in case you lost it by mistake or for whatever reason. It’s not a very common scenario, but hey you never know. I ran into this some time last week(Fortunately it’s in our POC environment), Okay, Here’s the deal – we have a POC SQL Instance which was installed by an individual who is no longer working with us and apparently he forgot to make our DBA grp as sysadmins. Basically we don’t have admin rights to our own SQL Instance, SA account is disabled(Well, No one has no clue what that pwd was to begin with). So, how did we recover from this disastrous event? Before going to the details on how I fixed it, below is where I started with.
- Since this is a POC box, we(DBA Grp) are already members of Local Admins on the server level. Okay, that’s a good start. In case you are not local admins at windows level, You should request your Windows admins to grant you those rights(At least temporarily for the duration you regain access to SQL)
- I stopped SQL Server services and started(Made sure Just the DB Engine is started) in single user mode by using “-m” switch as a startup parameter.
3. Now I connected to my SQL Server instance(You can use either SSMS or SQLCMD). I will show you how to use SSMS(Run as Admin just to be sure) using the trick I showed here.
Note: when using SSMS method, don’t hit connect in your “Connect to Server” window. You will probably end up with multiple connections and yours might not be the first connection(Spoiler: Object Explorer)
I hit cancel in the above window and I selected “Database Engine Query” and created our DBA Grp login and granted sysadmin rights.
4. Once that’s done, All I had to do was go to configuration manager and remove SQL from single user mode and restart my services and Voilaa we are in as Sysadmins! If you are curios why it worked, SQL Server when started in single user mode allows any user who is a member of BUILTIN\ADMINISTRATORS group at windows level login as sql server admin. This is by design to recover form these kind of situations.
I hope you won’t get into this awkward situation, but you are probably here reading this blog post because you are dealing with this right now 🙂 Hope this helps. Cheers!