Execute As works in SQL2005 but not when called by ASP page

Question

Execute As works in SQL2005 but not when called by ASP page

ringalsbe

SSC Rookie

Points: 34
More actions
January 25, 2010 at 9:25 am

#219847

I created a user that is only allowed to execute stored procedures and not SELECT on a table. I have a stored procedure that uses the EXECUTE AS another user with select permissions on a table. When I login as the restricted user in SQL Server Management Studio, the stored procedure works like it should and returns the correct records. When I run the stored procedure with ASP code using this same userid, the stored procedure does not return any records. If I grant the user select access to the table used by the stored procedure, the ASP application returns the correct records using the same stored procedure. Why does it appear that EXECUTE AS is being ignored when calling the stored procedure from ASP?

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184395 More actions · Answer 1

What is the error message you getting returned from the asp call?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

ringalsbe SSC Rookie Points: 34 More actions · Answer 2

I am not getting an error message. It just isn't returning any records.

Jack Corbett SSC Guru Points: 184395 More actions · Answer 3

Can you post your asp code? There has to be something wrong there because you should either get an error or data? Do you get the column header information from the procedure?

Why do you need to use Execute AS?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

ringalsbe SSC Rookie Points: 34 More actions · Answer 4

"rs.Open cmdTemp, , 1, 3" returns rs.EOF= True and rs.recordcount=0. If I give the user permissions on the table and run the same code, rs.recordcount = 1.

Jack Corbett SSC Guru Points: 184395 More actions · Answer 5

Can you run Profiler when you execute the asp page and post the results? Just add the Error And Warnings: User Error Message to the Standard Template and you should get all you need.

Can you also provide the entire asp code block?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

ringalsbe SSC Rookie Points: 34 More actions · Answer 6

The asp code is:

<%@ Language=VBScript %>

<%

dim Conn, cmdTemp, rs, S_String

Session("Projects_ConnectionString")= "DSN=SQL2005;UID=DB_User;APP=Microsoft (R) Developer Studio;WSID=SQL2005;DATABASE=DB_Dev"

Session("Projects_ConnectionTimeout")= 15000

Session("Projects_CommandTimeout")= 30000

Session("Projects_RuntimeUserName")= "DB_User"

Session("Projects_RuntimePassword")= "xxxxxxxxxxxxxxxxxxxxx"

Session.Timeout= 240

Server.ScriptTimeOut= 300

Set Conn = Server.CreateObject("ADODB.Connection")

Conn.ConnectionTimeout = Session("Projects_ConnectionTimeout")

Conn.CommandTimeout = Session("Projects_CommandTimeout")

Conn.Open Session("Projects_ConnectionString"), Session("Projects_RuntimeUserName"), Session("Projects_RuntimePassword")

Set cmdTemp = Server.CreateObject("ADODB.Command")

cmdTemp.CommandType = 1

Set cmdTemp.ActiveConnection = Conn

Set rs = Server.CreateObject("ADODB.Recordset")

%>

<HTML>

<HEAD>

</HEAD>

<BODY>

<%

S_String = "EXEC DB_Developer.qry_GetTestData @Id_no = 3"

cmdTemp.CommandText = S_String

rs.Open cmdTemp, , 1, 3

If rs.recordcount > 0 Then

rs.MoveFirst

Response.Write ("The answer is " & rs("First_Name") & " " & rs("Last_Name"))

Else

Response.Write ("Record not found")

End If

rs.close

%>

</BODY>

</HTML>

The stored procedure is:

CREATE PROCEDURE [DB_Developer].[qry_GetTestData] (

@Id_no int)

WITH EXECUTE AS OWNER

AS

SELECT * FROM tblTest

WHERE Id_no = @Id_no

When DB_User is given permission to execute the stored procedure but not select the table, the result is "Record not found". When the same code is used after giving DB_User select permission on the table, the first name and last name belonging to the record is printed.

Jack Corbett SSC Guru Points: 184395 More actions · Answer 7

What happens if you put fully qualify the table (schema.tblTest)? You really should fully qualify objects regardless, but I'm guessing that somehow this is your issue.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question