How to use RunAs command for SSMS if option does not exist

Problem

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.

c:\>runas /user:aa\jshah “C:\Program Files\Microsoft SQL Server\100\Tools\Binn

\VSShell\Common7\IDE\Ssms.exe”

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
• Read these other security tips

Jugal Shah

Biography

Jugal Shah is the founder of SQLDBPool.com, where he is writes articles on SQL Server Administration and Development. Jugal has 8+ years of extensive experience in SQL Server Database administration and development. Jugal has worked on SQL Server 2000, 2005, 2008 and 2008 R2. In his current assignment, Jugal is managing large critical databases. His expertise is in database security, performance tuning and implementing high availability solutions. He can be contacted at jugal.shah@sqldbpool.com. He has been recognized as a SQL Server MVP since 2010.

• MSSQLTips Awards: Trendsetter (25+ tips) – 2013