Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wiltech
Yak Posting Veteran
51 Posts |
 Posted - 2002-10-18 : 11:40:11
|
| Ok why is it whenever I edit a UDF in EM, it always clears the permissions? Stored procedures and views don't do this, they keep their permissions. Yeah yeah I know that when i edit it through EM is actually doing a drop/create.Anyone know if this is a known issue with MS?ThanksTony W |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-18 : 11:55:25
|
| You answered your own question: EM does a drop/create. When you drop an object naturally its permissions go with it. If that behavior is unacceptable, then use ALTER FUNCTION instead...that's what it's there for.BTW, it is unreasonable to expect MS to "be aware" or "know about" this "issue", which isn't an issue at all. Nor should they change EM's behavior to accommodate this. It's pretty easy to just use ALTER and you'll find it's a lot more powerful to do so that to use EM exclusively. |
 |
|
|
wiltech
Yak Posting Veteran
51 Posts |
Posted - 2002-12-17 : 15:22:37
|
| OK, finally got back around to this again.I tried using the ALTER FUNCTION command in query analyzer to alter it and it still resets the user permissions.ThanksTony |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2002-12-17 : 16:14:10
|
| Sounds like you've encountered a bug because an alter does not reset permissions. Which service pack are you running? Have you checked MS' knowledge base to see if it is in fact a bug? |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-12-17 : 17:38:51
|
Editing a function through EM does not DROP/CREATE it runs ALTER FUNCTION with the express purpose of maintaining the object permissions. However  there is a bug with ALTER FUNCTION if the UDF is a multi statement table valued function, can't find a KB article just now.What service pack are you on and what type of UDF is it (scalar/inline table/multi statement) ?HTHJasper Smith |
|
|
|
wiltech
Yak Posting Veteran
51 Posts |
Posted - 2002-12-18 : 12:16:39
|
quote:
Editing a function through EM does not DROP/CREATE it runs ALTER FUNCTION with the express purpose of maintaining the object permissions. However there is a bug with ALTER FUNCTION if the UDF is a multi statement table valued function, can't find a KB article just now.
Yeah I wasn't aware of the ALTER FUNCTION function when I orignally posted.quote:
What service pack are you on and what type of UDF is it (scalar/inline table/multi statement) ?Jasper Smith
SP2 & It's a Table UDFThanksTony W |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-12-18 : 17:22:25
|
I have done some follow up and there is no KB article and this bug "probably" won't be fixed in SQL2000  . However, if you apply changes in the form of scripts which include the object permissions this should not be an issue. Changes to production should not be made via EM or ALTER statements but via scripted versions of the procedure/function that include DROP/CREATE and object permissions.HTHJasper Smith |
|
|
|
|
|
|
UDF and Permissions