To manipulate SQL Server metadata from a remote client…
Posted by danielmeyer on April 6, 2010
(If you’d rather skip the narrative and go straight to the solution, please see the Solution heading…)
We use virtual machines for a lot of our testing — you just clone a VMWare LabManager image, deploy it, and off you go. You can do whatever you want to the configuration, because when you’re done you just undeploy and delete the workspace. It’s really nice.
The “clone a LabManager image” step works because someone regularly takes one of the nightlyish builds and installs it to a VM, creating an image suitable for others to clone.
For less generally applicable needs, such as builds of the team branch I’m working in, an image is not always available, but we can take the main branch and apply transformations to it to update it to the team branch.
And now we’ve arrived at the topic of this post. One of the tranformations I commonly find myself needing to apply is a database schema update.
I can copy the SQL Server update scripts to the VM and run MS SQL Server Management Studio on the VM. This is ok, but it requires that I Remote Desktop in to the VM, establish access to the VM’s file shares, and copy files from two different directories… and SQL Server Management Studio is sluggish on the VM.
I would prefer to run SQL Server Management Studio on my local PC, connecting to the VM… but the user account doesn’t have permissions to alter columns or create or drop tables. Do I have to use Remote Desktop after all?
Edit the properties of your user in MS SQL Server Management Studio under Security->Logins (you might need to get on the VM to do this) and add the
sysadmin server role to your user. Now you can create and drop tables, alter columns, etc. from a remote SQL Server Management Studio connection.