To help promote the seperation of duties one of the things my company has done is to divide our permissions into two accounts. We have one account that is for our daily tasks. Reading email, searching the internet, basic structure changes in a database etc. The other account is our admin account. It’s for remoting to servers, security tasks, really anything that requires sysadmin. I’m not going to argue the advisability of this because honestly, I’m kind of on the fence. That said, I do have to deal with it and there are a few tips in case you have to deal with it as well.

Running SSMS as an alternate AD account

Since these are AD accounts if I just open SSMS and use trusted authentication then I’m going to be using my basic account. I want to be able to use SSMS using my admin account though. In order to do this hold the shift key down and right click on your shortcut for SSMS, then select Run as different user.

Then enter your admin AD id. Normally you don’t have to type in your username and password when connecting to SQL unless you are using a SQL Id but this is another windows login screen.

Permissions on your box

It’s important to remember that the permissions used by this version of SSMS (the one you opened above) are for that other account. This means if you have files in your documents directory, or on your desktop that you normally use they aren’t going to be available. For example, you do a file-open and go to your desktop or documents directory you might be a bit surprised when it’s empty, or nearly so. This is because while your normal documents directory is in this path: C:\Users\RegularUserName\Documents\ your admin account is pointing to C:\Users\AdminUserName\Documents\. So all you need to do is make sure you are explicitly pointing to the Users\RegularUserName path right? Nope. Your Admin account isn’t going to have permissions to your regular accounts directories unless you explicitly give it those permissions. Even then it can be a bit of a pain.

Knowing which version of SSMS you are using

When doing this I usually end up with two versions of SSMS open. One with my regular account and one with my admin account. Now, it’s easy enough to tell which account a connection is using (left is a query window, right is object explorer):

But you actually have to think about it, and I like a big visual clue. To that end, my regular id has a white background and my admin id has a black background. When I hit alt-tab it looks like this:

And you can see it makes it really simple to tell the difference even when doing an alt-tab.

Jump box

Another way to handle the admin account and avoid the above problems is to use a jump box. Basically another workstation within the network that you can remote to in order to do your admin work. This has a lot of advantages. For example, if you are working remotely and run the risk of your connection timing out or your internet being flaky you can run into performance problems or worst than that a long-running query getting rolled back because you completely lost your connection. If your jump box is located within the normal network for your office then not only will performance be better but the risk of losing your connection greatly diminishes. Jump boxes can also help with firewalls. You place the jump box within the firewall and then open a hole in the firewall specifically for the jump box. Last (probably not but we will call it that for this post) but not least all connections from this jump box will default to using the admin account and you don’t really have to think about it much.

All of that said, I hate having to do all of my work through an RDP window if I can at all help it. Not to mention this can be an expensive option depending on your internal cost of having to have an extra machine just for you to RDP into.