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

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 FileName, and then click Save.
   
   Note FileName 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.

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