Is there a way in SQL Server Management Studio 2005 (or later) to change the Windows Authentication user (as you could in SQL Server 2000 and older)?
This is the general connection properties dialog I am referring to (note the greyed out UID/PWD when selecting Windows Auth; sorry, I'm new so can't embed an img).
FYI - One workaround is to use 'runas' but I'm looking for a solution that will allow me to work with multiple Windows accounts across multiple servers (across multiple domains) . . .
No.
Could you in SQL Server 2000 enterprise manager? I don't recall because Windows auth uses the current logged in user because it relies on the NT login token generated at login time.
The only way in "Run As".
Don't you have trust between the domains?
There are many places where someone might want to deploy this kind of scenario, but due to the way integrated authentication works, it is not possible.
As gbn mentioned, integrated authentication uses a special token that corresponds to your Windows identity. There are coding practices called "impersonation" (probably used by the Run As... command) that allow you to effectively perform an activity as another Windows user, but there is not really a way to arbitrarily act as a different user (à la Linux) in Windows applications aside from that.
If you really need to administer multiple servers across several domains, you might consider one of the following:
Hopefully this helps!
The only way to achieve what you want is opening several instances of SSMS by right clicking on shortcut and using the 'Run-as' feature.
One other way that I discovered is to go to "Start" > "Control Panel" > "Stored Usernames and passwords" and add the domain account that you would use with the "runas" command.
Then, in SQL Management Studio 2005, just select the "Windows Authentication" and input the server you wanna connect to (even though the user that you can see greyed out is still the local user)... and it works!
Don't ask me why ! :)
While there's no way to connect to multiple servers as different users in a single instance of SSMS, what you're looking for is the following RUNAS syntax:
runas /netonly /user:domain\username program.exe
When you use the "/netonly" switch, you can log in using remote credentials on a domain that you're not currently a member of, even if there's no trust set up. It just tells runas that the credentials will be used for accessing remote resources - I believe the application interacts with the local computer as the current user, and interacts with remote computers as the user you've given.
You'd still have to run multiple instances of SSMS, but at least you could connect as different windows users in each one.