Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2011-03-02
12
Medium Priority
?
645 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
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

580 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