My company uses different Windows users for the various DWH environments (Development, Test, Acceptance & Production). How can I connect to a SQL Server instance in SQL Server Management Studio (SSMS) with a different user so that I can still use Windows Authentication?
 |
| Windows Authentication |
Solution
The quick solution is to hold the Shift-key while right clicking the SSMS shortcut in the start menu. Then the 'Run as different user' option appears, which allows you to enter different credentials. After that the user name field for the Windows Authentication changes.
 |
| Right click the shortcut and choose Run as different user |
 |
| Now you can run SSMS with a different account |
 |
| Now the User name changes |
Runas shortcut
A more permanent solution is to create a new shortcut with a runas command in it. Instead of the standard SSMS command (see Target):
"C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"
you use:
RUNAS /user:myDomain\myUserName /savecred "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"
(110 = SQL 2012 / 120 = SQL 2014 / 130 = SQL 2016 / 140 = SQL 2017)
- The /user: option allows you to use a different user.
- The /savecred option will save the password after the first time (not in Windows 7 Home)
- For more options check out this site or execute "runas /?" in the command prompt to show all options. Some forums/blogs recommend the /netonly option to only use the provided user for remote access, but that often doesn't work causing SSMS not to start.
 |
| Change Target field and optionally the Comment |
To finish this off: click on the Change Icon button and browse to SSMS.exe to select the familiar icon.
 |
| Change icon to finish off the shortcut |
The first time you will see a command prompt where you have to enter your password. If you added the /savecred option then the second time you will only see a short 'flash' of the command prompt and then SSMS will start. You could get rid of it by changing the Run property to minimized (after the first execution).
 |
| Enter password |
SSMS commandline options
You can even extend this solution with some extra options for SSMS itself. Like providing the instance and database name.
 |
| SSMS command line options |
Summary
In this post you saw how you can start up SSMS with a different domein user so you can still use Windows Authentication. This not only works for SSMS, but for other programs like Visual Studio as well:
RUNAS /user:myDomain\myUserName /savecred "C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\devenv.exe"
