Microsoft KB Archive/888530

= BUG: The permissions for a multi-statement table-valued function are not retained when you use the ALTER FUNCTION statement in SQL Server 2000 =

Article ID: 888530

Article Last Modified on 1/31/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



Bug #: 355483 (SQL Server 8.0)



SYMPTOMS
In Microsoft SQL Server 2000, when you use an ALTER FUNCTION statement to modify a multi-statement table-valued function, the permissions that you previously granted for the multi-statement table-valued function are not retained after you execute the ALTER FUNCTION statement.

Note Scalar and inline table-valued functions do not exhibit this problem.



WORKAROUND
To work around this problem, you must use the GRANT statement to re-create the permissions for the multi-statement table-valued function after you have performed the ALTER FUNCTION statement.

To save the permissions that were previously defined for the multi-statement table-valued function, use the Generate SQL Scripts command. To do this, follow these steps:
 * 1) Start Enterprise Manager.
 * 2) In the Console Root, expand Microsoft SQL Servers.
 * 3) Expand SQL Server Group, and then expand the server that contains the function that you are modifying.
 * 4) Expand Databases, and then expand the database that contains the function that you are modifying.
 * 5) Click User Defined Functions.
 * 6) Right-click the function that you want to save the permissions for, and then click All Tasks.
 * 7) Click Generate SQL Scripts, and then click the Options tab.
 * 8) Under Security Scripting Options, click the Script object-level permissions check box, and then click OK.
 * 9) In the File name text box, type , and then click Save.

Note  is the name of the Transact-SQL script file.

You can copy the GRANT statements that you want to use from the Transact-SQL script file that you saved. Paste the GRANT statements in to the current ALTER FUNCTION Transact-SQL script that you are working on.



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the &quot;Applies to&quot; section.



Steps to reproduce the behavior
To reproduce this behavior, run the following Transact-SQL script: EXEC sp_addlogin 'UDFTest' EXEC sp_adduser 'UDFTest','UDFTest' GO IF OBJECT_ID('dbo.multi1') IS NOT NULL DROP FUNCTION dbo.multi1 GO CREATE FUNCTION dbo.multi1 (@arg1 int) RETURNS @numbers table (number int) AS BEGIN INSERT INTO @numbers VALUES(1) RETURN END GO GRANT ALL ON dbo.multi1 TO UDFTest go sp_helprotect NULL, 'UDFTest'  -- 5 rows returned go ALTER FUNCTION dbo.multi1 (@arg1 int) RETURNS @numbers table (number int) AS BEGIN INSERT INTO @numbers VALUES(2) RETURN END GO sp_helprotect NULL, 'UDFTest'  -- 0 rows returned GO After you run the Transact-SQL script, the last sp_helprotect stored procedure returns 0 (zero) rows, and you receive the following informational message in Query Analyzer:

Server: Msg 15330, Level 11, State 1, Procedure sp_helprotect, Line 346 There are no matching rows on which to report.

Additional query words: SQL2000 UDF

Keywords: kbtshoot kbbug KB888530

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.