Solved

sql server: An invalid option was specified for the statement "CREATE/ALTER FUNCTION".

Posted on 2011-03-02
12
558 Views
Last Modified: 2012-05-11
i try this
CREATE FUNCTION GetOrders ()
RETURNS TABLE
WITH EXECUTE AS 'pat'
AS
RETURN ( SELECT * FROM Sales.SalesOrderDetail )

but i have this error
Msg 487, Level 16, State 1, Procedure GetOrders, Line 4
An invalid option was specified for the statement "CREATE/ALTER FUNCTION".
Msg 178, Level 15, State 1, Procedure GetOrders, Line 5
A RETURN statement with a return value cannot be used in this context.
0
Comment
Question by:enrique_aeo
  • 6
  • 4
  • 2
12 Comments
 
LVL 33

Expert Comment

by:knightEknight
Comment Utility
You will need to declare a table variable and populate it, then return it from the function:


AS
BEGIN

 declare @ret table( col1 int, col2, varchar(255) ... )

 insert into @ret
 select col1, col2 ...
 from Sales.SalesOrderDetail

 return @ret

END
0
 
LVL 15

Expert Comment

by:pateljitu
Comment Utility
WITH EXECUTE will work under login that created the function.

CREATE FUNCTION GetOrders()
RETURNS TABLE
-- WITH EXECUTE AS 'pat'
AS
RETURN ( SELECT * FROM Sales.SalesOrderDetail
0
 
LVL 33

Expert Comment

by:knightEknight
Comment Utility
actually, the table structure must be specified in the RETURNS statement:


  CREATE FUNCTION GetOrders ()
  RETURNS @ret table( col1 int, col2, varchar(255) ... )
  -- WITH EXECUTE AS 'pat'
  AS
  BEGIN
   insert into @ret
   select col1, col2 ...
   from Sales.SalesOrderDetail

   return @ret

  END
0
 
LVL 33

Expert Comment

by:knightEknight
Comment Utility
... which raises the question, why use a function for this when you can just use the query instead?
0
 

Author Comment

by:enrique_aeo
Comment Utility
I want to prove this, as users create pat and  ted

executionAS.jpg
0
 
LVL 33

Expert Comment

by:knightEknight
Comment Utility
I don't know if the code in that image was meant to be taken literally - I have my doubts.  Is this for SQL Server?
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:enrique_aeo
Comment Utility
yes, sql server 2008 oficial course
0
 
LVL 33

Expert Comment

by:knightEknight
Comment Utility
I was wrong about that syntax - it appears it does work in 2008.  I created a generic function with this exact text and it worked:

  CREATE FUNCTION myTableFunction ()
  RETURNS TABLE
  --WITH EXECUTE AS 'pat'
  AS
  RETURN ( SELECT 1 as one )

so, I would guess that the source of the error you are seeing is in the WITH EXECUTE clause, or with the database/table name.
Does "pat" exist as a user in the database?
0
 

Author Comment

by:enrique_aeo
Comment Utility
yes, pat exits
userEXISTS.jpg
0
 

Author Comment

by:enrique_aeo
Comment Utility
EXPERTS PLEASE I need your help (Transact SQL) to play the slide
0
 
LVL 33

Accepted Solution

by:
knightEknight earned 125 total points
Comment Utility
Per this documentation:  http://msdn.microsoft.com/en-us/library/ms188354.aspx

It looks like the EXECUTE AS clause cannot be used on table-valued functions.  Not sure why that would be the case, but that is what it says.

To verify this, I created a dummy function that returns INT and uses the EXECUTE AS clause, and it works just fine:

create function dropme()
  returns  INT
  WITH EXECUTE AS 'my_login'
  as
  begin
    declare @i int
    select  @i = 0
    return  @i
  end


But this does not work:

  CREATE FUNCTION myTableFunction ()
  RETURNS TABLE
  WITH EXECUTE AS 'my_login'
  AS
  RETURN ( SELECT 1 as one )

 
 
0
 
LVL 15

Assisted Solution

by:pateljitu
pateljitu earned 125 total points
Comment Utility
Do create function without WITH EXECUTE statement

CREATE FUNCTION GetOrders ()
RETURNS TABLE
AS
RETURN ( SELECT * FROM Sales.SalesOrderDetail )

Right-click on function and select properties > permissions
Click ADD and select 'pat', once the user is selected it will list explicit permission that can be assigned to user 'pat' GRANT select permission to user (do similar procedure for all user you want to GRANT / DENY).

Once the permission is set and if you try to execute Store Procedure with username as parameter, if you supply 'pat' as user, grant permission was set for user and procedure will return rows. And say similar 'ted' was denied select permission when parameter is 'ted' SP will return error.
Check Permission:


SELECT * FROM fn_my_permissions('GetProducts', 'OBJECT') 
    ORDER BY subentity_name, permission_name ;  
REVERT;
GO

Open in new window

create procedure sproc_GetOrders 

@user varchar(50) = ''

as            
begin            
set nocount on   

	EXECUTE AS USER = @user;
	BEGIN TRY
		select * from GetOrders();
	END TRY
	BEGIN CATCH
		-- Execute error retrieval routine.
		Print 'Error Occured;'
	END CATCH;

Set nocount off              
end

Open in new window

0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now