Change the active user in sp

Hello,
I would like to change the active user in a stored procedure
Ex. :
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 ?

Thank a lot

Felix Pageau
fpageau@.SPAMSUCK.str.caI'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.

-PatP|||Hello,

my problem is that the stored procedure contain dynamic sql. I need to execute the dynamic sql query without giving the user to query the table.

I thought that with a kind of "impersonation" I would be able to do so but I haven't found any.

Do you know a way to execute dynamic sql in another context than then one of the user that has called the sp ?

Thank for your reply|||The quickest and easiest answer is probably an extended stored procedure (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa2_67vp.asp). I don't know of anything in Transact-SQL that will allow you to do that.

-PatP|||Do you mean I should create an extended sp with my sql code or that I should call an existing extend sp ?|||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.

-PatP|||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.

Unix and SQL are entirely two different world.

Change Password of SQL Server Login

Can any one tell me how to change a password for a logged in user in sql
server? Here the condition is the logged in user is a user who has least
privileges and just will have only select permission on database tables. The
logged in user does not have any server roles and even he is not a member of
db_Owner role. He is not even member Security Administrator which is
required to change the password for a user.
Can any one give an example to change the password using T-Sql
statements for my scenario'
VenkatDo you mean change their own password or change another users password? Any
user can change their own password, if they need to change other users
passwords then they must be a member of the securityadmin or sysadmin fixed
server roles, there's no getting around that.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Venkat" <tammana@.inooga.com> wrote in message
news:%23gIjK%23paFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Can any one tell me how to change a password for a logged in user in sql
> server? Here the condition is the logged in user is a user who has least
> privileges and just will have only select permission on database tables.
> The logged in user does not have any server roles and even he is not a
> member of db_Owner role. He is not even member Security Administrator
> which is required to change the password for a user.
>
> Can any one give an example to change the password using T-Sql
> statements for my scenario'
>
> --
> Venkat
>
>