T-SQL command to log in as a different user?

Question

Is there a T-sql command, like the Oracle "Connect" which lets me change the user that I am logged in as?

Answer 1

You can use the EXECUTE AS command:

http://msdn.microsoft.com/en-us/library/ms181362.aspx

Answer 2

You can try SetUser 'username' too. To revert just run SetUser. Verify by running select suser_name()

Answer 3

Using sqlcmd.exe or Management Studio in SQLCMD mode, you can use the :connect macro to change the connection in the middle of a script. (See here for the full syntax -- weirdly, I can't find documentation for this on MSDN.)

If you just need to test something as a different principal, you can use EXECUTE AS to impersonate the principal, and REVERT to back out.

Answer 4

The question has multiple answers, dependent on the context of 'changing the user that I am logged in as.'

A public website will have one or a few 'user accounts' which are used by every site visitor. Even through those accounts, it may be necessary to elevate a permission to execute, for instance, dynamic SQL in solving a multivariable search requirement.

Errand Sommarskog has a very detailed discussion on the security and management of user accounts via T-SQL in stored procedures. http://www.sommarskog.se/grantperm.html