Issues with Run As to Start SQL Server Management Studio with a Different Login

By: Jugal Shah | Comments (3) | Related: > SQL Server Management Studio

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 servers. So in order to use the different login to connect to SQL Server using SSMS you need to use the "Run as" feature, but when doing this you may get this error message "The directory name is invalid". In this tip we look at how to resolve this issue.

Solution

As mentioned above when using the "Run as" feature you may get this error message: "The directory name is invalid" as shown below.

in order to use the different login to connect to SQL Server using SSMS you need to use the "Run as" feature, but when doing this you may get this error message

The reason this occurs is if the "Start in" property is set to %HOMEDRIVE%%HOMEPATH% on the Shortcut tab of the SSMS Properties dialog box. In Windows XP and Windows Server 2003, we cannot use the "Run as" feature to access the logged in user's mapped home drive.

SSMS Properties dialog box

To fix this problem we can do as follows:

Step 1:
Click Start, point to All Programs, and then point to SQL Server Management Studio.

Click Start, point to All Programs, and then point to SQL Server Management Studio

Step 2:
Right-click SQL Server Management Studio and then click Properties.

click SQL Server Management Studio and then click Properties

Step 3:
In the Start in box, change the value from %HOMEDRIVE%%HOMEPATH% to value %WINDIR%. and Click OK as shown below.

sql server management studio properties

Step 4:
Right Click on SQL Server Management Studio and Click on "Run as", if the Run as option does not come up press Shift and Right Click. At this point, specify the login account you want to use and SSMS should work for you.

Click on SQL Server Management Studio and Click on "Run as"

Next Steps

You can also use this solution with other programs such as Internet Explorer, and other tools if you are facing this kind of issue.
Take a look at these other tips about SQL Server Management Studio

About the author

Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.

View all my tips

Comments For This Article

Wednesday, April 24, 2013 - 3:19:18 AM - Collins Adoyo

Hi I introduced a new data and log files in the data folder as MSQL shown in this path:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSQL\Data

After attempting to attach the mdf file, it failed to attach referring to the exisiting folder. So I deleted the link to attach afresh. After this my database with data were deleted and cant get them. The only database remaining is the latest I copied there.

Any advice on how to get help and recover my lost data. Still I cannot attach the new mdf file.

Collins

Tuesday, November 23, 2010 - 6:30:10 AM - Jugal Shah

Press the Shift Key and Right Click on SSMS you can see the Run As... Option

Thank You, Jugal Shah

Tuesday, November 23, 2010 - 5:30:22 AM - mordino

I've successfully used the "Run as..." option for SQL Server Management Studio 2008 on Windows XP. But SQL Server Management Studio 2008 on Windows 7 i only do have the options of Open (meaning Run as current user) and Run as Administrator.

So i am not able anymore to connect with an Admin Account. Any advice how to re-enable the "Run as..." option for Windows 7?