Problem with change permission script for UDF

Brian DonahueBrian Donahue Posts: 6,590 New member
edited October 17, 2003 10:02AM in SQL Compare Previous Versions
Hi Radovan,

I've tried it here locally, and I only get a GRANT REFERENCES on my
group in the migration script. My UDF is supposed to return a table. Would
you mind sending the script of the UDF to [email protected], so we could
have a look? If not, that's okay, I'll just try different combinations of
things until I find the problem.

Thanks,

Brian Donahue
Red Gate Technical Support



"Radovan Dobriæ" <[email protected]> wrote in message
news:[email protected]
> I got following problem:
> I have one UDF
>
> CREATE FUNCTION fn_MyFunction (@param int,....)
> RETURNS @MyTABLE TABLE (myfield1 int ,....)
> AS
> begin
> insett into @Mytable (...)
> select ....
>
> return
> end
>
> I define select and DRI permission for one SQL User and one Database Role:
>
> Now I wont to generate SQL script for this object .
> I got following script (including only code for permission)
> ...
> GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON
> [dbo].[fn_MyFunction] TO [MyRole]
>
> GRANT REFERENCES , SELECT ON [dbo].[fn_MyFunction] TO [MyUser]
>
>
>
> Of cource, when I try to execute this script I get error for creating
> permission for Database Role:
> Server: Msg 4606, Level 16, State 1, Line 1
> Granted or revoked privilege UPDATE is not compatible with object.
>
>
> For other type of function except TABLE function I have good script.
>
>
> Radovan
>
>
>
>

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hi Radovan,

    Great! Thanks for writing back to tell us.

    Sincerely,

    Brian Donahue
    Red Gate Technical Support

    "Radovan Dobriæ" <[email protected]> wrote in message
    news:[email protected]
    > Hi,
    > I find the answer.
    > The problem was in SQL server 2000 sp3.
    > The same script I got within EM.
    > When I install sp3a the problem is gone.
    >
    > Thanks
    > Radovan
    >
    > "Brian Donahue (Red Gate)" <[email protected]> wrote in message
    > news:kZ%[email protected]
    > > Hi Radovan,
    > >
    > > I've tried it here locally, and I only get a GRANT REFERENCES on my
    > > group in the migration script. My UDF is supposed to return a table.
    Would
    > > you mind sending the script of the UDF to [email protected], so we
    > could
    > > have a look? If not, that's okay, I'll just try different combinations
    of
    > > things until I find the problem.
    > >
    > > Thanks,
    > >
    > > Brian Donahue
    > > Red Gate Technical Support
    > >
    > >
    > >
    > > "Radovan Dobriæ" <[email protected]> wrote in message
    > > news:[email protected]
    > > > I got following problem:
    > > > I have one UDF
    > > >
    > > > CREATE FUNCTION fn_MyFunction (@param int,....)
    > > > RETURNS @MyTABLE TABLE (myfield1 int ,....)
    > > > AS
    > > > begin
    > > > insett into @Mytable (...)
    > > > select ....
    > > >
    > > > return
    > > > end
    > > >
    > > > I define select and DRI permission for one SQL User and one Database
    > Role:
    > > >
    > > > Now I wont to generate SQL script for this object .
    > > > I got following script (including only code for permission)
    > > > ...
    > > > GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON
    > > > [dbo].[fn_MyFunction] TO [MyRole]
    > > >
    > > > GRANT REFERENCES , SELECT ON [dbo].[fn_MyFunction] TO [MyUser]
    > > >
    > > >
    > > >
    > > > Of cource, when I try to execute this script I get error for creating
    > > > permission for Database Role:
    > > > Server: Msg 4606, Level 16, State 1, Line 1
    > > > Granted or revoked privilege UPDATE is not compatible with object.
    > > >
    > > >
    > > > For other type of function except TABLE function I have good script.
    > > >
    > > >
    > > > Radovan
    > > >
    > > >
    > > >
    > > >
    > >
    > >
    >
    >
This discussion has been closed.