Query to connect to a database with different user

Hello:

Is there a way through T-SQL to run a store procedure and inside the store procedure, change the user, execute a select, and get back the original user that run the store procedure?

Thanks

In SQL 2005, yes there is.

Refer to Books Online, Topic: 'EXECUTE AS (Transact-SQL).

Example A demonstrates exactly what you are seeking.

|||

Thanks Arnie, is this possible in SQL 2000?|||

Unfortunately, no.

There is no easy way to cause security context switching.

|||You can use the Ad-Hoc remote quries, OPENROWSET & OPENDATASOURCE..

Query Timeout

Hi there,
I have a java application which connects to an sqlServer database and issues
an SQL update statement.
When you attempt to connect to the database if there is no connection then
this is fine, it will retry for x times and timeout at the time you have
set.
The problem is if you have made a valid connection and then disconnect the
database from the network and try to run the sql UPDATE. The update will
only attempt one time and basically hang until the connection is
re-established.
The other way round, if the machine the java application is running on is
disconnected it works fine. i.e. it attempt to send the UPDATE query fro x
times and times out at the interval set.
It only seems to hang if the java application is still connected but the
database is disconnected. Is this a general problem with the jdbc driver?
Regards
Jamie
Jamie wrote:

> Hi there,
> I have a java application which connects to an sqlServer database and issues
> an SQL update statement.
> When you attempt to connect to the database if there is no connection then
> this is fine, it will retry for x times and timeout at the time you have
> set.
> The problem is if you have made a valid connection and then disconnect the
> database from the network and try to run the sql UPDATE. The update will
> only attempt one time and basically hang until the connection is
> re-established.
> The other way round, if the machine the java application is running on is
> disconnected it works fine. i.e. it attempt to send the UPDATE query fro x
> times and times out at the interval set.
> It only seems to hang if the java application is still connected but the
> database is disconnected. Is this a general problem with the jdbc driver?
> Regards
> Jamie
It's at a lower level than that. The failure you cause means the TCP stack will
take minutes before it notifies the driver that the socket is dead. You might
try setting the query timeout on your statement before executing it. Then the
driver may be able to return control to you sooner.
Joe Weinstein at BEA

Query through a firewall

We have a SQL 2000 database sitting behind a CheckPoint firewall. The server
host our HelpSTAR helpdesk
database. Clients connect to the database from their workstation with the
HelpSTAR client. All the proper ports
are open to allow communication through the firewall between client and
workstation. We have several reports
that will not run. When I execute the following query I get the response
desired.
"SELECT * from tblrptfiles where id = 11"
When I run the same query but for a different id # it fails after about 30
seconds with the listed error message.
"SELECT * from tblrptfiles where id = 24"
Error message
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
If I run the same query that failed from a machine in the same subnet
without the firewall between them it works. Keep in mind that my query works
with the firewall inplace if I ask for a different id. If I run the query
with an ID that is not in the table it works, also the ID I am asking for
does exist in the table. Any ideas on what would be causing this?
Thanks.
This is a weird one. Are you sure it's not just different machines
requesting a different set of ID's? Have you made sure all the clients and
SQL Server are using the latest MDAC?
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"Tubbaguts" wrote:

> We have a SQL 2000 database sitting behind a CheckPoint firewall. The server
> host our HelpSTAR helpdesk
> database. Clients connect to the database from their workstation with the
> HelpSTAR client. All the proper ports
> are open to allow communication through the firewall between client and
> workstation. We have several reports
> that will not run. When I execute the following query I get the response
> desired.
> "SELECT * from tblrptfiles where id = 11"
> When I run the same query but for a different id # it fails after about 30
> seconds with the listed error message.
> "SELECT * from tblrptfiles where id = 24"
> Error message
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> If I run the same query that failed from a machine in the same subnet
> without the firewall between them it works. Keep in mind that my query works
> with the firewall inplace if I ask for a different id. If I run the query
> with an ID that is not in the table it works, also the ID I am asking for
> does exist in the table. Any ideas on what would be causing this?
> Thanks.
|||We discovered our issue with this query through the firewall late yesterday.
Our Check Point firewall also has an intrusion prevention running and the
firewall was seeing this query and one other as being a SQL exploit. Once we
allowed this query through everything worked again.
"MeanOldDBA" wrote:
[vbcol=seagreen]
> This is a weird one. Are you sure it's not just different machines
> requesting a different set of ID's? Have you made sure all the clients and
> SQL Server are using the latest MDAC?
> --
> MeanOldDBA
> derrickleggett@.hotmail.com
> http://weblogs.sqlteam.com/derrickl
> When life gives you a lemon, fire the DBA.
>
> "Tubbaguts" wrote:

Query through a firewall

We have a SQL 2000 database sitting behind a CheckPoint firewall. The server
host our HelpSTAR helpdesk
database. Clients connect to the database from their workstation with the
HelpSTAR client. All the proper ports
are open to allow communication through the firewall between client and
workstation. We have several reports
that will not run. When I execute the following query I get the response
desired.
"SELECT * from tblrptfiles where id = 11"
When I run the same query but for a different id # it fails after about 30
seconds with the listed error message.
"SELECT * from tblrptfiles where id = 24"
Error message
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForD
ata
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
If I run the same query that failed from a machine in the same subnet
without the firewall between them it works. Keep in mind that my query works
with the firewall inplace if I ask for a different id. If I run the query
with an ID that is not in the table it works, also the ID I am asking for
does exist in the table. Any ideas on what would be causing this?
Thanks.This is a weird one. Are you sure it's not just different machines
requesting a different set of ID's? Have you made sure all the clients and
SQL Server are using the latest MDAC?
--
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"Tubbaguts" wrote:

> We have a SQL 2000 database sitting behind a CheckPoint firewall. The serv
er
> host our HelpSTAR helpdesk
> database. Clients connect to the database from their workstation with the
> HelpSTAR client. All the proper ports
> are open to allow communication through the firewall between client and
> workstation. We have several reports
> that will not run. When I execute the following query I get the response
> desired.
> "SELECT * from tblrptfiles where id = 11"
> When I run the same query but for a different id # it fails after about 30
> seconds with the listed error message.
> "SELECT * from tblrptfiles where id = 24"
> Error message
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckFo
rData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> If I run the same query that failed from a machine in the same subnet
> without the firewall between them it works. Keep in mind that my query wor
ks
> with the firewall inplace if I ask for a different id. If I run the query
> with an ID that is not in the table it works, also the ID I am asking for
> does exist in the table. Any ideas on what would be causing this?
> Thanks.|||We discovered our issue with this query through the firewall late yesterday.
Our Check Point firewall also has an intrusion prevention running and the
firewall was seeing this query and one other as being a SQL exploit. Once we
allowed this query through everything worked again.
"MeanOldDBA" wrote:
[vbcol=seagreen]
> This is a weird one. Are you sure it's not just different machines
> requesting a different set of ID's? Have you made sure all the clients an
d
> SQL Server are using the latest MDAC?
> --
> MeanOldDBA
> derrickleggett@.hotmail.com
> http://weblogs.sqlteam.com/derrickl
> When life gives you a lemon, fire the DBA.
>
> "Tubbaguts" wrote:
>

Query string with quote

I want to connect to table of AS400 with a parameter(THE NAME OF TABLE IS
AVP.M002)
The following sentence execute correctly.
SELECT * FROM LIB01."AVP.M002" WHERE CDTAB= '007'
How will be the string with the parameter: Parameters!ParmCDTAB.Value
I was trying with the string , but it doesn't run
="SELECT * FROM LIB01."AVP.M002" WHERE CDTAB= '" &
Parameters!ParmCDTAB.Value &"'"
The problem is ( "AVP.M002") . How I build the string to execute correctly?Just try this (duplicate the double quotes):
="SELECT * FROM LIB01.""AVP.M002"" WHERE CDTAB= '" &
Parameters!ParmCDTAB.Value &"'"
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Walter" <Walter@.discussions.microsoft.com> wrote in message
news:2E79D5D2-0169-4F16-9308-0F8C124203F8@.microsoft.com...
>I want to connect to table of AS400 with a parameter(THE NAME OF TABLE IS
> AVP.M002)
> The following sentence execute correctly.
> SELECT * FROM LIB01."AVP.M002" WHERE CDTAB= '007'
> How will be the string with the parameter: Parameters!ParmCDTAB.Value
> I was trying with the string , but it doesn't run
> ="SELECT * FROM LIB01."AVP.M002" WHERE CDTAB= '" &
> Parameters!ParmCDTAB.Value &"'"
>
> The problem is ( "AVP.M002") . How I build the string to execute
> correctly?
>