As a best practice in the industry, a DBA often has two logins that are used to access SQL Server; one is their normal Windows login and the other is an admin level login account which has sysAdmin rights on the SQL Server boxes. In addition most of the time the SQL Server client tools are only installed on the local desktop and not on the SQL Server Production Box. In order to use the different login to connect to SQL Server using SSMS you need to use the "Run as" feature. What do you do in the case of Windows 7 or Windows Vista where you can't find the Run As Different User option.
Solution
If the Run As different user option is not available while you right click on the SSMS executable or shortcut, you can follow one of the techniques below to use the run as option.
Technique 1 - Run As different User
Step 1: Press and Hold the Shift Key and Right Click on the SSMS executable or shortcut, you should see the Run as different user option in the context menu.
Step 2: Once you click on the Run as different user option the below dialog box will appear. You have to enter the User Name or Domain\User Name and Password to run the application with that user's security context.
Technique 2 - Run As different User using command prompt
Step 1: Go to the command prompt and type runas /? and you should see the Run As help as shown below.
Step 2: Enter the below command in the command prompt to open SSMS with the different user's security context. It will prompt you for the password; enter the password to run the application. Here I am entered "aa\jshah" for the domain\user to use.
After I run the above comand I am prompted for the password as shown below.
Setup a Shorcut to do this
If you are going to use the secondary ID to always to connect to SQL Server, you can create a shortcut to open SSMS with the secondary ID context as shown below.
Right click on the desktop, go to New and click Shortcut.
Paste or type the above Run As command into the Target box. Then the next time you open SSMS just use this shortcut to use the Secondary ID.
Next Steps
If you are not already doing this, you should look into using the two Windows accounts security standards in your organization's database environment
Run SSMS on the local machine instead of having to login to the production box
Hi there. This is a great workaround and article. Do you have any means of doing the same for Azure Data Studio on Mac? I tried finding some options but was to no avail.
This process also works with SmartCards as well. If you create a shortcut, Windows will briefly open a command window where it will give you progress updates and allow you to enter your PIN.
Thursday, December 31, 2020 - 9:09:07 AM - Walter Prins
For those running into "Unable to acquire user password", you should try including /netonly, since then it removes the need for a trust relationship between the local and the remote domain. E.g the following should work (from admin prompt):
runas /netonly /user:domain\user "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe"
(adjust the path as needed - you can find it with from a command prompt with the dommand "dir C:\ssms.exe /s")
Saturday, September 19, 2020 - 12:09:06 AM - Jugal
did you try running it from command prompt? I am able to run the command for the SSMS 2016 on Windows 10. Could you please confirm the source OS version, SSMS version and do you have admin permission on the client server?
Hi, thanx for this column. but I am facing problem when I run SSMS through RunAs command instead of "Run As Different User" by the same credentials i cant Connect to some of SQL servers (not all). I receive error:
TITLE: Microsoft SQL Server Management Studio ------------------------------
I've also been required to use a Desktop account (for email, Word, Excel) and an Admin account for anytime I log into a server. I had learned the "{SHIFT} Run As" trick, but my desktop is locked down so hard that it then wants an account that is Admin on the desktop in order to proceed. How could we do both RUN AS (different account) and "Run As Administrator"?
Thursday, March 8, 2012 - 8:36:09 AM - Michael J. Swart
Wouldn't it be great if we could connect to a database using different Windows credentials from within SQL Server? I wonder if there's an Microsoft Connect item for that.
Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.