> Hey John,
>
> Using your script above, I could not reproduce the error; however, MSDN
> revealed the following bug:
>
> http://support.microsoft.com/default.aspx?scid=kb;en-us;888530
>
> which I could reproduce.  I'm running SQL Server 2000 SP3a; what
> version are you running?
>
> Stu

>Hello to all the almighties,
>
>I run script below...
>
>/* --- S C R I P T --- */
>CREATE TABLE tblMyTable (
>    MyTableID int PRIMARY KEY CLUSTERED IDENTITY (1, 1) NOT NULL ,
>    [Name] nvarchar (50) NULL
>)
>GO
>INSERT INTO tblMyTable ([Name]) VALUES (N'Tom')
>INSERT INTO tblMyTable ([Name]) VALUES (N'Dick')
>INSERT INTO tblMyTable ([Name]) VALUES (N'Harry')
>GO
>CREATE VIEW MyView
>AS
>SELECT *
>FROM tblMyTable
>GO
>GRANT SELECT ON MyView To MyRole
>GO
>CREATE PROCEDURE MyStoredProcedure
>    @MyTableID int
>AS
>SELECT *
>FROM tblMyTable
>WHERE MyTableID = @MyTableID
>GO
>GRANT EXECUTE ON MyStoredProcedure To MyRole
>GO
>CREATE FUNCTION dbo.MyFunction()
>RETURNS TABLE
>AS
>RETURN ( SELECT MyTableID
>FROM dbo.tblMyTable )
>GO
>GRANT SELECT ON MyFunction To MyRole
>GO/* --- S C R I P T --- */
>
>Then I call ALTER FUNCTION without the GRANT statement...
>
>/* --- S C R I P T --- */
>ALTER VIEW MyView
>AS
>SELECT *
>FROM tblMyTable
>GO
>ALTER PROCEDURE MyStoredProcedure
>    @MyTableID int
>AS
>SELECT *
>FROM tblMyTable
>WHERE MyTableID = @MyTableID
>GO
>ALTER FUNCTION dbo.MyFunction()
>RETURNS TABLE
>AS
>RETURN ( SELECT MyTableID
>FROM dbo.tblMyTable )
>GO
>/* --- S C R I P T --- */
>
>And have painfully discovered the PERMISSION has been lost.
>
>HISTORY
>=======
>We are using SQL-DMO COM objects to remote administer our sizable
>database. Our goal is to recompile all views, stored procedures and
>user-defined functions after table changes have been made. We can
>confirm the permissions remain for all views and stored procedures
>however, UDF do not. In addition, we can replicate this issue in Query
>Analyzer using the above script.
>
>I am not worthy...
>
>J
>
> 
>

> John,
>
> What kind of changes are you making to tables that necessitate,
> but don't automatically cause, the invalidation of cached plans?
>
> And can you run DBCC FREEPROCCACHE instead of ALTERing all your
> procedures, views, and functions?
>
> Steve Kass
> Drew University
>
>
> John wrote:
>
> >Hello to all the almighties,
> >
> >I run script below...
> >
> >/* --- S C R I P T --- */
> >CREATE TABLE tblMyTable (
> >    MyTableID int PRIMARY KEY CLUSTERED IDENTITY (1, 1) NOT NULL ,
> >    [Name] nvarchar (50) NULL
> >)
> >GO
> >INSERT INTO tblMyTable ([Name]) VALUES (N'Tom')
> >INSERT INTO tblMyTable ([Name]) VALUES (N'Dick')
> >INSERT INTO tblMyTable ([Name]) VALUES (N'Harry')
> >GO
> >CREATE VIEW MyView
> >AS
> >SELECT *
> >FROM tblMyTable
> >GO
> >GRANT SELECT ON MyView To MyRole
> >GO
> >CREATE PROCEDURE MyStoredProcedure
> >    @MyTableID int
> >AS
> >SELECT *
> >FROM tblMyTable
> >WHERE MyTableID = @MyTableID
> >GO
> >GRANT EXECUTE ON MyStoredProcedure To MyRole
> >GO
> >CREATE FUNCTION dbo.MyFunction()
> >RETURNS TABLE
> >AS
> >RETURN ( SELECT MyTableID
> >FROM dbo.tblMyTable )
> >GO
> >GRANT SELECT ON MyFunction To MyRole
> >GO/* --- S C R I P T --- */
> >
> >Then I call ALTER FUNCTION without the GRANT statement...
> >
> >/* --- S C R I P T --- */
> >ALTER VIEW MyView
> >AS
> >SELECT *
> >FROM tblMyTable
> >GO
> >ALTER PROCEDURE MyStoredProcedure
> >    @MyTableID int
> >AS
> >SELECT *
> >FROM tblMyTable
> >WHERE MyTableID = @MyTableID
> >GO
> >ALTER FUNCTION dbo.MyFunction()
> >RETURNS TABLE
> >AS
> >RETURN ( SELECT MyTableID
> >FROM dbo.tblMyTable )
> >GO
> >/* --- S C R I P T --- */
> >
> >And have painfully discovered the PERMISSION has been lost.
> >
> >HISTORY
> >=======
> >We are using SQL-DMO COM objects to remote administer our sizable
> >database. Our goal is to recompile all views, stored procedures and
> >user-defined functions after table changes have been made. We can
> >confirm the permissions remain for all views and stored procedures
> >however, UDF do not. In addition, we can replicate this issue in Query
> >Analyzer using the above script.
> >
> >I am not worthy...
> >
> >J
> >
> > 
> >

> Hi Steve,
>
> The most common table change is the addition or removal of a field.
>
> In many instances, a recompile using the ALTER statement raises any
> issues within an affected databse object like a view, stored procedure
> or function. This helps prevent any runtime errors. Eg. ADO calls.
>
> I can confirm the DBCC FREEPROCCACHE statement does affect any of the
> USER-DEFINED FUNCTION permissions hence resolving our issue. However,
> the only foreseeable concern is not knowing if any of the database
> objects contain a broken reference(s).
>
> And since the count of database objects to check number in the
> thousands it is virtually impossible to test them all; therefore a
> degree of due diligence will be applied.
>
> With that been said, I have implemented your suggestion into our
> solution and will let the group know if further issues arise.
>
> Thank you in advance,
>
> J
>
> Steve Kass wrote:
> > John,
> >
> > What kind of changes are you making to tables that necessitate,
> > but don't automatically cause, the invalidation of cached plans?
> >
> > And can you run DBCC FREEPROCCACHE instead of ALTERing all your
> > procedures, views, and functions?
> >
> > Steve Kass
> > Drew University
> >
> >
> > John wrote:
> >
> > >Hello to all the almighties,
> > >
> > >I run script below...
> > >
> > >/* --- S C R I P T --- */
> > >CREATE TABLE tblMyTable (
> > >    MyTableID int PRIMARY KEY CLUSTERED IDENTITY (1, 1) NOT NULL ,
> > >    [Name] nvarchar (50) NULL
> > >)
> > >GO
> > >INSERT INTO tblMyTable ([Name]) VALUES (N'Tom')
> > >INSERT INTO tblMyTable ([Name]) VALUES (N'Dick')
> > >INSERT INTO tblMyTable ([Name]) VALUES (N'Harry')
> > >GO
> > >CREATE VIEW MyView
> > >AS
> > >SELECT *
> > >FROM tblMyTable
> > >GO
> > >GRANT SELECT ON MyView To MyRole
> > >GO
> > >CREATE PROCEDURE MyStoredProcedure
> > >    @MyTableID int
> > >AS
> > >SELECT *
> > >FROM tblMyTable
> > >WHERE MyTableID = @MyTableID
> > >GO
> > >GRANT EXECUTE ON MyStoredProcedure To MyRole
> > >GO
> > >CREATE FUNCTION dbo.MyFunction()
> > >RETURNS TABLE
> > >AS
> > >RETURN ( SELECT MyTableID
> > >FROM dbo.tblMyTable )
> > >GO
> > >GRANT SELECT ON MyFunction To MyRole
> > >GO/* --- S C R I P T --- */
> > >
> > >Then I call ALTER FUNCTION without the GRANT statement...
> > >
> > >/* --- S C R I P T --- */
> > >ALTER VIEW MyView
> > >AS
> > >SELECT *
> > >FROM tblMyTable
> > >GO
> > >ALTER PROCEDURE MyStoredProcedure
> > >    @MyTableID int
> > >AS
> > >SELECT *
> > >FROM tblMyTable
> > >WHERE MyTableID = @MyTableID
> > >GO
> > >ALTER FUNCTION dbo.MyFunction()
> > >RETURNS TABLE
> > >AS
> > >RETURN ( SELECT MyTableID
> > >FROM dbo.tblMyTable )
> > >GO
> > >/* --- S C R I P T --- */
> > >
> > >And have painfully discovered the PERMISSION has been lost.
> > >
> > >HISTORY
> > >=======
> > >We are using SQL-DMO COM objects to remote administer our sizable
> > >database. Our goal is to recompile all views, stored procedures and
> > >user-defined functions after table changes have been made. We can
> > >confirm the permissions remain for all views and stored procedures
> > >however, UDF do not. In addition, we can replicate this issue in Query
> > >Analyzer using the above script.
> > >
> > >I am not worthy...
> > >
> > >J
> > >
> > > 
> > >