Problem with change permission script for UDF
Brian Donahue
Posts: 6,590 Bronze 1
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 support@red-gate.com, 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æ" <radovan@ccb.hr> wrote in message
news:TW2FB$YiDHA.1472@server53...
> 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
>
>
>
>
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 support@red-gate.com, 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æ" <radovan@ccb.hr> wrote in message
news:TW2FB$YiDHA.1472@server53...
> 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.
Comments
Great! Thanks for writing back to tell us.
Sincerely,
Brian Donahue
Red Gate Technical Support
"Radovan Dobriæ" <radovan@ccb.hr> wrote in message
news:mKVG4j7kDHA.2184@server53...
> 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)" <brian.donahue@red-gate.com> wrote in message
> news:kZ%23T5$CjDHA.1500@server53...
> > 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 support@red-gate.com, 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æ" <radovan@ccb.hr> wrote in message
> > news:TW2FB$YiDHA.1472@server53...
> > > 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
> > >
> > >
> > >
> > >
> >
> >
>
>