When reverse engineering via an ADO connection to a SQL Server 2008 database, the "EXECUTE AS" clause for the Stored Procedure is not displayed correctly in the General tab of the Stored Procedure Properties screen.
Steps to Reproduce:
To test this, you would have to create a User-Defined Table Type and then create a stored procedure using this User-Defined Table Type as parameter type and a table-valued function resp.
1. Execute the following script on a SQL Server 2008 database:
CREATE TYPE [dbo].[myTableType] AS TABLE(
[userName] [sysname] NOT NULL,
[changeDateTime] [datetime2](5) NULL
)
GO
CREATE PROCEDURE [dbo].[ProcWithUDTT]
@myUDTT dbo.myTableType READONLY
WITH EXECUTE AS OWNER
AS
BEGIN
return 0;
END
go
-- TVF example
CREATE FUNCTION [dbo].[myTVF]
(
@param1 as int,
@param2 char(2),
@param3 as tinyint
)
RETURNS TABLE
AS
RETURN
Select 1 as 'col1';
GO
2. Launch TDM and Reverse Engineer the above database via an ADO connection to the SQL Server database.
3. In the Physical Model Explorer, double-click on the Stored Procedure
Actual Results:
The "Execute As" clause shows "CALLER" when it should display "OWNER" as defined in the above script.