SQL Server - Connect as another domain user

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.

Solution 1:

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"

Solution 2:

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