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


By:   |   Updated: 2010-11-12   |   Comments (3)   |   Related: More > 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


Last Updated: 2010-11-12


get scripts

next tip button



About the author
MSSQLTips author Jugal Shah 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
Related Resources





Comments For This Article




Wednesday, April 24, 2013 - 3:19:18 AM - Collins Adoyo Back To Top (23535)

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 Back To Top (10388)

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 Back To Top (10387)

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?



download





Recommended Reading

Different Options for Query Results in SQL Server Management Studio

Display Line Numbers in a SQL Server Management Studio Query Window

Connect to SQL Servers in another domain using Windows Authentication

Executing large scripts in SQL Server Management Studio with Insufficient Memory Failures

SQL Server Database Diagram Index was Outside the Bounds of the Array














get free sql tips
agree to terms