Dealing with multiple AD accounts
8November 19, 2018 by Kenneth Fisher
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.
SQL Studies 
I would recommend “Run as Administrator” instead of “Run as Different User.” If you are logged on to your workstation with a non-administrator account, you will be prompted to enter an administrator logon account. In addition, you can change the properties of the shortcut to always run as administrator saving you the extra step of right-clicking. If you are logged in as a local administrator, run as administrator will not prompt you for credentials.
I also recommend not being logged on to your workstation with an administrator account. It lowers the risk of being infected with malware. It also can save you from some “oops actions” because you must be more deliberate in administrative functions by the extra action of running as administrator.
That is a common misunderstanding I see at my office actually. “Run as Administrator” makes the tool act as the admin on the box and will not prompt you if you take administrative actions. “Run as Different User” let’s you run as a seperate account. In this case it is our “Admin” account but that’s “Admin” as in we are performing admin type accounts, not “admin” on the box. In fact our Admin accounts don’t actually have administractive permissions on our boxes. Just on the SQL Servers (in my case) or web servers in the case of the web admins etc.
“Run as Administrator” will prompt for user account provided the logon account used is not an administrator on the local machine. I use this on a daily basis. To clarify, I have a non-privilege account (JSmith) and a privileged account (sa-JSmith). JSmith is not an administrator on my local machine, but sa-JSmith is. When logged on as JSmith, when I select run as administrator, I am prompted for an administrator logon and I use the credentials for sa-JSmith.
If my non-privileged account (JSmith) was a local administrator, when I select to run as an administrator on my local machine, I would be prompted with a Yes/No prompt.
Run as administrator would not work when the account used to log on to SQL Server is not a local administrator on your local machine.
Microsoft has documented this at https://docs.microsoft.com/en-us/windows/security/identity-protection/user-account-control/how-user-account-control-works.
Ahh fair point. I’ve just always used run as new account. Why would you want to run the sa-jsmith as an administrator on the box though? I don’t know there is much of a benefit is there?
[…] Kenneth Fisher shows how to use different Active Directory credentials when using SQL Server Managem…: […]
I find your use of the word ‘versions’ in the phrase “When doing this I usually end up with two versions of SSMS open …” confusing. This would normally be called having two “instances” of the SSMS application running, not two “versions.”
Sorry. There are so many ways to say things and it can be difficult to get it right at times. I could also have said “copies” and that would have been confusing to some people as well. I do see your point though.
[…] in order to test we have two options. Either test using a windows/AD id by running SSMS as an alternate ID or create another SQL Id and use impersonation again. I’m using the SQL Id here but I did […]