Search code examples
sqlsql-serversql-server-2005execute

SQL - Impersonate SYSTEM_USER

Is there a way to impersonate or change the SYSTEM_USER on MS SQL 2005?

I have many views (written by a third party) which I can not change which references an SYSTEM_USER to "ID Table". 

... AND idCode = SUBSTRING(SYSTEM_USER, CHARINDEX('\', SYSTEM_USER) + 1, LEN(SYSTEM_USER))

*I do have rights to the tables that the views pull from, BUT these views have the added SYSTEM_USER.*

Thanks.

Solution

  • Check out the Execute As Transact SQL

    Providing you have the correct permissions you can execute any T-SQL as another user and then revert back to the original connection credentials.

    Select System_User
Go

Execute As Login = 'SomeOtherLogin'   
   Select System_User
Revert
Go

Select System_User
Go

    This will output the current connection credentials for the first and third select and output the specified credentials for the second select.