Regain Administrative Rights to SQL Server

Ever run into a situation where you are an administrator on a machine, but your account is not an administrator in SQL server? Read below for my situation and the solution I found to fix it!

The project I’m on currently has a shared virtual machine that is given to new developers when they come onto the project. It’s done this way because of some legacy software SDKs that are installed that only work on Windows XP, as well as some legacy VB6 code that requires the IDE to compile properly. I’m not complaining about this, but the virtual machine was created with Microsoft VirtualPC and I happen to be working on a Mac. Rather than booting my bootcamp VM, and starting the VM inside there (tedious and slow), I opted to migrate the VPC image to a VMWare Fusion image.

The transition was not easy, and required several steps that were not intuitive, but I finally got there and the VM is responsive and performs fairly well now. But I ran into a problem – the VM relied on share Windows authentication that mapped my Bootcamp user with a user in the VM called ‘dev’. Dev had administrative rights to the SQL Server, but during the transition the account disappeared and I lost all rights to do anything in the database other than connect!

I found a script that will map a given user to a given SQL server instance with the sysadmin rights. This saved me a ton of time and got me up and working again. The script does require administrative rights to the machine, so it should not be a security concern. All in all, I’m very happy to have regained access to my 2008 SQL Express instance so SSMS can enable intellisense on this rather complex database!