Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We use Windows Authentication for connecting to our internal SQL Server instances (a mix of 2005 and 2008).

When I open SQL Server Management Studio and connect to a server it obviously connected me using my current domain credentials.

Is it possible to somehow override this and log on as an alternative domain user?

The aim is to check that users have been set up correctly and can access the DBs that I want them to.

I know I could login to a windows session as them, and do it that way, but that seems a tad tedious.

share|improve this question

3 Answers 3

up vote 6 down vote accepted

After doing some digging it appears that the only way to achieve this is via the runas command:

runas /user:domain\user "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

Or for a 64 Bit Environment, use the 32 bit Program Files Folder : runas /user:domain\user "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

share|improve this answer
1  
This is available through the windows UI as well. Right click on a shortcut and there should be the option for "Run As..." –  pipTheGeek Aug 10 '09 at 12:02
    
Nice - I've added this as a .bat file to my shortcuts! –  Sam Aug 10 '09 at 16:14

IIRC, if you map a drive to the server as the user you want to use, before doing anything else on the server, when you connect with integrated security the credentials that you have already established on that server will be used. I used to do this when I was working from a workstation that was not trusted by the domain that the servers were in. You still only get one set of credentials, this trick won't work for two different sets of credentials.

Also, the TSQL SETUSER command can be useful here. It lets you impersonate someone, sort of like su does on linux. The 2008 documentation says that this is deprecated, but I think that it still works.

share|improve this answer
    
SETUSER is replaced with EXECUTE AS. –  K. Brian Kelley Aug 11 '09 at 19:43

You won't be able to log in as them if you do this, but you will be able to validate their permissions within SQL Server. If you are using T-SQL, you can use the EXECUTE AS command to impersonate the user within SQL Server (use REVERT to switch back). For instance:

EXECUTE AS LOGIN = 'MyDomain\SomeUser';
GO

SELECT name FROM mydb.sys.objects;
GO

REVERT;
GO
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.