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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

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

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

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

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools