Execute as Caller

This is a followup to my previous question.
Example SP:
CREATE PROCEDURE [dbo].[ChangeWorkspace]
@.UserName varchar(32),
@.Workspace varchar(32)
WITH EXECUTE AS CALLER
AS
BEGIN
update table1
set workspace = @.Workspace
where username = @.Username;
END
In the DB table1 is in a schema: user1 (user1.table1)
I connect via ASP.Net with a simple connection string that logs in with User
Id= user1:
In Asp.Net I run the Stored procedure
cmd.ExecuteNonQuery();
The error is:
System.Data.SqlClient.SqlException: Invalid object name 'table1'.
***so I assume its looking for dbo.table1 -- now if in my sp I use the
schema prefix it works:
update user1.table1s
set workspace = @.MapWorkspace
where username = @.Username;
So the problem is why doesn't the SP act as if I did user1.table1 since in
the SP I have WITH EXECUTE AS CALLER
Thanksdev648237923 (dev648237923@.noemail.noemail) writes:
> So the problem is why doesn't the SP act as if I did user1.table1 since in
> the SP I have WITH EXECUTE AS CALLER
Because the procedure uses the default schema of the procedure owner.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi dev648237923,
I agree with Erland that the problem you met is caused by the "dbo" schema
name of the stored procedure, the stored procedure's code will inherit the
schema context from the procedure's schema. Actually stored procedure is
designed to be schema specific, therefore, when we specify a schema name
for one sp, its contained T-SQL code will also followup that schema
context. While the "EXECUTE AS" is mainly for security context purpose.
For your scenario, the target table's schema name should match the stored
procedure's schema name , like:
CREATE PROCEDURE [user1].[ChangeWorkspace]
...............
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi dev648237923,
Have you got any further ideas on this issue? If there is still anything we
can help, please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)