6

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

5

You can use the EXECUTE AS command:

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

2
  • Future users: Be really careful using EXECUTE AS on a connection from a connection pool (ie: any SqlConnection in .NET): it automatically resets the connection to prevent security leaks, which will break all the rest of the active connections from that pool. Furthermore, this will only happen sporadically when you're in a threaded environment, so you might not figure it out for months. Not that I know anyone who that happened to... Nov 19 '14 at 15:50
  • If you want to bust the pooling you can add "Pooling=false" to your connection string in .NET (Pooling in the SqlConnectionStringBuilder). I recognize that these tips aren't 100% DBA specific, but they might bite users reading this. Nov 19 '14 at 15:52
1

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

2
0

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.

-1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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