If you want to test how something works for the little people (i.e. Those without system admin permissions on SQL), SQL 2005 introduced the EXECUTE AS command.
Any command run after this, will run in the context of the specified account
The EXECUTE AS is reverted back to the logged in user with the REVERT command
* The ORIGINAL_LOGIN() shows which the original login - as expected!
This is simple to implement, and allows you to run commands under the guise of the specified user, the following example should demonstate its use:
--Open a normal connection to a sql server
SELECT USER_NAME(),ORIGINAL_LOGIN()
dbo,rnorris
EXECUTE AS USER = 'smallPerson'
SELECT USER_NAME(),ORIGINAL_LOGIN()
smallPerson,rnorris
REVERT
SELECT USER_NAME(),ORIGINAL_LOGIN()
dbo,rnorris
As usual, full documentation and examples can be found in BOL