I would like to change the active user in a stored procedure
I'm logged on sql as "userA".
I call the stored procedure "spGetInfo".
In the first line of "spGetInfo" I would like to do something like "su poweruser", query some data and do "su system_user"
Is there anything like the unix "su" command in SQL ?
I'm not quite sure what you are asking. Assuming that the user has the permission to execute a stored procedure, that stored procedure executes in the security context of the user that CREATES the procedure. If the dbo creates a procedure, the procedure can do anything that the dbo can do when any user runs it.
The only exception is that dynamic SQL always runs in the context of the currently logged in user. That can hang you up, but otherwise you should be fine.
If you need to "switch context" to allow dynamic SQL to execute, I'm suggesting that you write an extended stored procedure that allows you to control what gets executed and how. I don't know of a way to get you what you want using just Transact-SQL.
Even if you change the user inside your stored procedure from simple user to system admin user, the proc would still be running under the simple user access rights.
Best would be to define the access rights using the role, stored procedure etc etc and then control the sql.