Like many people who work in IT departments, my company gives me two accounts to their network. One is a “regular” user account – one with the standard permissions every employee gets. The other is one I can use to do my IT work – it has administrator privileges to many systems, including all our SQL Servers. The purpose of this two layer approach is to make it harder for trojans, viruses, and other malware to be able to infiltrate our network. All my mundane work – email, search the web, etc. – is done with my low privileged account. If by some chance my computer gets infected, because I’m using an account with few privileges, odds are low that a piece of malware will be able to migrate into our most important systems because the infected account won’t have access. If I need to do some IT admin-type work, I launch the program I need using Window’s “Run As Another User” command (shift-right-click an executable to get this option).

This works well for most programs, but causes problems with some, like SQL Server Management Studio. Actually, SSMS works this way without problem.  The problem comes when you want to add registered servers or customize the way SSMS is set up. As soon as you close the program, the changes you made are lost. If you re-start the program, even using the same alternate account, your changes are lost.

For a DBA, this is something of a pain in the butt. My low privileged account has no access to my SQL Servers, but my admin-level account does. And because I’ve got about 80+ SQL Servers to manage, I like to make use of SSMS’ registered servers feature to keep a full list of servers a click away, not to mention all the customizations I have made to SSMS to get it looking and working the way I like.

Luckily, there is a way around this. The Microsoft Program runas.exe lets you tell Windows to run a program using a user’s current network environment instead of the local environment. the full details and switches of the runas program can be found at this TechNet article.

For SSMS, modify the program’s shortcut target to be this:

C:\Windows\System32\runas.exe /noprofile /env /user:<domain>\<admin-level-account> “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe \”C:\Users\<normal-level-account>\Documents\SQLSer~1\Projects\Utility ScriptsSSMS2012Ver.ssmssln\” “

Substitute your login accounts where noted and modify the paths as appropriate for your environment. The last command (specifying the .ssmssln file) is the registered server definition file to load.

Now, when you double-click the shortcut, you’ll see a DOS window open and you’ll be prompted to enter your admin-level account password. Then the app will launch. There is one drawback to this method: if you type your password wrong, it can be hard to tell. The DOS windows closes almost immediately after you hit Enter. If you entered your password correctly, SSMS will launch after a brief loading delay. If you entered the password incorrectly, the DOS window disappears and the program never launches. You may end up waiting for for 2 to 3 minutes while nothing happens before you realize the program isn’t going to launch.

Note that if you do not use Windows Authentication to connect to any of your SQL Servers, you do not need to do this because your Windows credentials are not passed to SQL Server in that case.