Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

4 Answers 4

up vote 5 down vote accepted

You can use the EXECUTE AS command:

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

share|improve this answer
    
Just what I was looking for - thank you! –  Daniel Williams Aug 16 '12 at 3:46

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.

share|improve this answer

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

share|improve this answer

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

share|improve this answer
    
    
select suser_name() appears to show the login in use for me rather than the user name. Think the the call for getting user name is select DB_Name(). +1 because I didnt know how to do this either though! –  Jonny Leeds Mar 27 at 14:00

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.