Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-03-02
12
Medium Priority
?
619 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
12 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 35021302
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
ID: 35021316
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
ID: 35021379
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 33

Expert Comment

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

Author Comment

by:enrique_aeo
ID: 35021626
I want to prove this, as users create pat and  ted

executionAS.jpg
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35021648
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
 

Author Comment

by:enrique_aeo
ID: 35022299
yes, sql server 2008 oficial course
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35022345
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
ID: 35033742
yes, pat exits
userEXISTS.jpg
0
 

Author Comment

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

Accepted Solution

by:
knightEknight earned 500 total points
ID: 35036528
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 500 total points
ID: 35039340
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Copy Database Wizard 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.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

688 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