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 recordset position

Hi
Table Structure
company name
Phone No
Products
Example
Company Name Phone No Products
Sri balaji Enterprises 26554432 Advertising Agents
KarthicK Call Taxi 43215434 Call Taxi
Chitra Electricals 65437654 Electricals
Udhayam Call Taxi 24358901 Call Taxi
Annamalaiyar Advertising 67542317 Advertising Agents
Mathi Vehicles 23456789 Automobiles
Chennai Media 45326754 Advertising Agents
OUTPUT FORM
Here i am using 2 combo boxes. first box list out all distinct products.(i.e)
First Combo Box loads
Advertising Agents
Automobiles
Call Taxi
Eletricals
if i click the Advertising agents in first combo box the 2nd combox will
shows the company names of who are all dealing Advertising.
so the second combo box shows
Sri balaji Enterprises
Annamalaiyar Advertising
Chennai Media.
As the same when i click call taxi
Karthick Call taxi
Udhayam Call taxi
Its working...But my problem is..........
Next time when i click Advertising agents the second combo box have to show
Annamalaiyar Advertising(2nd record have 2 come to first)
Chennai Media.(3rd record have 2 come second)
Sri balaji Enterprises(1st record have 2 go last)
same when i click call taxi
Udhayam Call Taxi(2nd record have 2 come to first)
Karthick Call taxi(1st record have 2 go last)
i dont know how to do...if anybody knows help me to do that...
Thanks in advance.
Hi
"nila_vaanan" wrote:

> Hi
> Table Structure
> company name
> Phone No
> Products
> Example
> Company Name Phone No Products
> Sri balaji Enterprises 26554432 Advertising Agents
> KarthicK Call Taxi 43215434 Call Taxi
> Chitra Electricals 65437654 Electricals
> Udhayam Call Taxi 24358901 Call Taxi
> Annamalaiyar Advertising 67542317 Advertising Agents
> Mathi Vehicles 23456789 Automobiles
> Chennai Media 45326754 Advertising Agents
> OUTPUT FORM
> Here i am using 2 combo boxes. first box list out all distinct products.(i.e)
> First Combo Box loads
> Advertising Agents
> Automobiles
> Call Taxi
> Eletricals
> if i click the Advertising agents in first combo box the 2nd combox will
> shows the company names of who are all dealing Advertising.
> so the second combo box shows
> Sri balaji Enterprises
> Annamalaiyar Advertising
> Chennai Media.
> As the same when i click call taxi
> Karthick Call taxi
> Udhayam Call taxi
> Its working...But my problem is..........
> Next time when i click Advertising agents the second combo box have to show
> Annamalaiyar Advertising(2nd record have 2 come to first)
> Chennai Media.(3rd record have 2 come second)
> Sri balaji Enterprises(1st record have 2 go last)
> same when i click call taxi
> Udhayam Call Taxi(2nd record have 2 come to first)
> Karthick Call taxi(1st record have 2 go last)
> i dont know how to do...if anybody knows help me to do that...
> Thanks in advance.
>
You don't say how this listbox is being populated, but SQL Server does not
guaranteed and the order of records returned by a SQL query unless you
specify an ORDER BY clause on your statement. Check out the section on ORDER
BY in books online.
John

change recordset position

Hi
Table Structure
company name
Phone No
Products
Example
Company Name Phone No Products
Sri balaji Enterprises 26554432 Advertising Agents
KarthicK Call Taxi 43215434 Call Taxi
Chitra Electricals 65437654 Electricals
Udhayam Call Taxi 24358901 Call Taxi
Annamalaiyar Advertising 67542317 Advertising Agents
Mathi Vehicles 23456789 Automobiles
Chennai Media 45326754 Advertising Agents
OUTPUT FORM
Here i am using 2 combo boxes. first box list out all distinct products.(i.e
)
First Combo Box loads
Advertising Agents
Automobiles
Call Taxi
Eletricals
if i click the Advertising agents in first combo box the 2nd combox will
shows the company names of who are all dealing Advertising.
so the second combo box shows
Sri balaji Enterprises
Annamalaiyar Advertising
Chennai Media.
As the same when i click call taxi
Karthick Call taxi
Udhayam Call taxi
Its working...But my problem is..........
Next time when i click Advertising agents the second combo box have to show
Annamalaiyar Advertising(2nd record have 2 come to first)
Chennai Media.(3rd record have 2 come second)
Sri balaji Enterprises(1st record have 2 go last)
same when i click call taxi
Udhayam Call Taxi(2nd record have 2 come to first)
Karthick Call taxi(1st record have 2 go last)
i dont know how to do...if anybody knows help me to do that...
Thanks in advance.Hi
"nila_vaanan" wrote:

> Hi
> Table Structure
> company name
> Phone No
> Products
> Example
> Company Name Phone No Products
> Sri balaji Enterprises 26554432 Advertising Agents
> KarthicK Call Taxi 43215434 Call Taxi
> Chitra Electricals 65437654 Electricals
> Udhayam Call Taxi 24358901 Call Taxi
> Annamalaiyar Advertising 67542317 Advertising Agents
> Mathi Vehicles 23456789 Automobiles
> Chennai Media 45326754 Advertising Agents
> OUTPUT FORM
> Here i am using 2 combo boxes. first box list out all distinct products.(i
.e)
> First Combo Box loads
> Advertising Agents
> Automobiles
> Call Taxi
> Eletricals
> if i click the Advertising agents in first combo box the 2nd combox will
> shows the company names of who are all dealing Advertising.
> so the second combo box shows
> Sri balaji Enterprises
> Annamalaiyar Advertising
> Chennai Media.
> As the same when i click call taxi
> Karthick Call taxi
> Udhayam Call taxi
> Its working...But my problem is..........
> Next time when i click Advertising agents the second combo box have to sho
w
> Annamalaiyar Advertising(2nd record have 2 come to first)
> Chennai Media.(3rd record have 2 come second)
> Sri balaji Enterprises(1st record have 2 go last)
> same when i click call taxi
> Udhayam Call Taxi(2nd record have 2 come to first)
> Karthick Call taxi(1st record have 2 go last)
> i dont know how to do...if anybody knows help me to do that...
> Thanks in advance.
>
You don't say how this listbox is being populated, but SQL Server does not
guaranteed and the order of records returned by a SQL query unless you
specify an ORDER BY clause on your statement. Check out the section on ORDER
BY in books online.
John