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
