Impersonate a user in SQL2005

It's often useful to run a stored procedure or any script with the permissions of another user (i.e. impersonate a different user than the current one).

It is possible to do so since SQL 2005 is out there, and is as simple as shown below:

EXECUTE AS USER = 'anotheruser'
GO

--Your SQL Statement

REVERT
GO

Note the REVERT command to restore the original user.

In order to impersonate a user, you will grant the IMPERSONATE rights on the user you want to impersonate to the user who will be impersonated it.

GRANT IMPERSONATE ON USER:: [AnotherUser] TO [MyCurrentUser];

Users with sysadmin role can impersonate any user, without being granted the IMPERSONATE right.

For all details, check the online reference @ http://msdn.microsoft.com/en-us/library/ms181362.aspx