DETAILS SOUGHT: Renaming this user-defined function removes any existing permissions and extended p

[Malcolm Cook]

H'lo,

When developing an ADP, renaming a user defined function using ACC2002 front
end against SQL Server 2000, you get prompted with:

Renaming this user-defined function removes any existing permissions
and extended properties.

I find that it does NOT in fact alter the existing permissions; rather, any
grants made in SQL Server survive the rename. At least in my minimal
testing.

However, as warned, extended properties are wiped.

Dooh!

Why is this? I wonder, because, when I simply use sp_rename directly in SQL
Server, there is no loss of expended properties (nor permissions).

I also wonder what effect I might expect this to have on Access. There are
a bunch of expended properties that Access uses for something, but this is
not documented anywhere. They all begin with MS_. It makes me leery.

The lesson, I think, is just use sp_rename on the server. However, maybe MS
WANTS the extended properties wiped. Maybe allowing them to survive a
rename such as happens using sp_rename will bollix up Access somehow else,
and that is why renaming them through the Access UI trashes these
properties... on pupose.

Who knows? Is this right?

I care because I sometimes need to rename UDFs and I use my own extended
properties on them.... so I need to make sure I 'm doing this "right".

Any knowledge on this out there?

Thanks!

 

[John Eikanger [MSFT]]

Hi, Malcolm

I'll need to research this issue.. Can you confirm that you see the same
behavior on the nwind database?

Thanks,

John Eikanger
Microsoft Developer Support

This posting is provided “AS IS” with no warranties, and confers no rights.
--------------------
| From: "Malcolm Cook" <[email protected]>
| Subject: DETAILS SOUGHT: Renaming this user-defined function removes any
existing permissions and extended properties.
| Date: Sun, 27 Jun 2004 02:49:41 -0500
| X-Tomcat-NG: microsoft.public.access.adp.sqlserver
|
| H'lo,
|
| When developing an ADP, renaming a user defined function using ACC2002
front
| end against SQL Server 2000, you get prompted with:
|
| Renaming this user-defined function removes any existing permissions
| and extended properties.
|
| I find that it does NOT in fact alter the existing permissions; rather,
any
| grants made in SQL Server survive the rename. At least in my minimal
| testing.
|
| However, as warned, extended properties are wiped.
|
| Dooh!
|
| Why is this? I wonder, because, when I simply use sp_rename directly in
SQL
| Server, there is no loss of expended properties (nor permissions).
|
| I also wonder what effect I might expect this to have on Access. There
are
| a bunch of expended properties that Access uses for something, but this is
| not documented anywhere. They all begin with MS_. It makes me leery.
|
| The lesson, I think, is just use sp_rename on the server. However, maybe
MS
| WANTS the extended properties wiped. Maybe allowing them to survive a
| rename such as happens using sp_rename will bollix up Access somehow else,
| and that is why renaming them through the Access UI trashes these
| properties... on pupose.
|
| Who knows? Is this right?
|
| I care because I sometimes need to rename UDFs and I use my own extended
| properties on them.... so I need to make sure I 'm doing this "right".
|
| Any knowledge on this out there?
|
| Thanks!
|
| --
| Malcolm Cook - (e-mail address removed)
| Database Applications Manager - Bioinformatics
| Stowers Institute for Medical Research - Kansas City, MO USA
|
|
|

 

[Mingqing Cheng [MSFT]]

Hi Malcolm,

From your descriptions, I understood you would like to know why you will
gto the error when renaming UDF in SQL Server. Have I understood you? If
there is anything I misunderstood, please feel free to let me know

First of all, as the error message shows, extended properties will be
wiped. It's right!

Secondly, Have you used SQL Profiler to trace the stroed procedures called,
could you confirm it is sp_rename that called?

Thridly, Would you please show me a sample, by which I could reproduce your
issue? I believe more detailed descriptions will get us closer to the
resolution

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!


Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support

 

[Malcolm Cook]

Hi John,

I am mistaken. The warning is correct on both counts: permissions are lost
and extended properties are lost.

Tracing it with SQL profiler. A new UDF is created with the new name before
dropping the old one (drop function).

sp_rename is NOT used. Perhaps it should be?

Thanks,

Malcolm Cook

 

[Mingqing Cheng [MSFT]]

Hi Malcolm,

Based on my test, sp_rename is NOT used either. I think it's internal issue
and by design.

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!


Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support

 

[Malcolm Cook]

Mingqing,

If there is any possibility of learning why sp_rename was chosen to not be
used, I would appreciate the lesson.

Thanks,

Malcolm Cook

 

[Mingqing Cheng [MSFT]]

Hi Malcolm,

I am not sure why sp_rename was not used for this kind of rename. However,
if you use sp_rename to change the object name in Query Analyzer, you will
receive the following error message

Caution: Changing any part of an object name could break scripts and stored
procedures.
The object was renamed to '<NewObjectName>'.

I think this caution could explain why drop and create was selected to use
intead of sp_rename. In the meanwhile, there are many known issue for
sp_rename that will lead to unsuitable scenraio. It's o keep the safe and
integrity of Access that, I believe, the developer of Access choose drop
and create instead of sp_rename

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!


Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support

 

[Malcolm Cook]

Hi Mingqing,

I am not sure why sp_rename was not used for this kind of rename. However,
if you use sp_rename to change the object name in Query Analyzer, you will
receive the following error message

Caution: Changing any part of an object name could break scripts and stored
procedures.
The object was renamed to '<NewObjectName>'.

I think this caution could explain why drop and create was selected to use
intead of sp_rename.

Well, drop and create will similarly break any scripts that refer to the
UDF.

And, if another SQL Server object is 'schema bound' to the UDF I want to
rename, both approaches (sp_rename and drop/create) will protect against
this.

Trying to sp_rename such a UDF generated (appropriately)
Server: Msg 15336, Level 16, State 1, Procedure sp_rename, Line 383
Object 'f_dog' cannot be renamed because the object participates in
enforced dependencies.

and trying to let access rename it (using create/drop under the hood)
generates
ADO error: Cannot DROP PROCEDURE 'dbo.f_dog' because it is being
referenced by object 'fdog_2'

NOTE! The create/drop approach is not atomic. If the drop fails, the
create still occurred, leaving two objects behind.

I think this is further reason that MS Access should use sp_rename 'under
the hood'.

Or, what am I missing?

John Eikanger, are you still tracking this thread???

In the meanwhile, there are many known issue for
sp_rename that will lead to unsuitable scenraio.

Oh? Can you tell me what they are? I am finding it to be just the other
way around!

Sincerely,

Malcolm Cook

 

[Wade Jackson [MSFT]]

Hi Malcolm

My name is Wade Jackson and I'm in the Microsoft Access Developer Support
group. I've received your case asking for clarification on the reasoning
for an ADP using a Drop/Create scenario when renaming a UDF, rather than
using an sp_Rename.

Realistically, I need to set your expectation that while I do have an
avenue to ask this type of question of the developers, I can't guarantee a
comprehensive answer. For instance, some of the information may be
proprietary, or simply not available. Unfortunately, after a product is
designed and released, we're more in a position to answer "How do you" than
"Why did you", and to accept suggestions for improvements to future
products (which are always welcome).

I am sure that as we dig into this we will find that it is a matter of
tradeoffs, and I take your question as needing to know what the tradeoffs
are in using one technique over another. In the end, we may be able to
post this as a wish/request to use sp_Rename instead of a Drop/Create
scenario in ADP's.

That said, I'll get the wheels in motion, and will try to get you an
answer!

Best Regards

Wade Jackson MCSD
Microsoft Access Developer Support

 

[Wade Jackson [MSFT]]

Hi Malcolm

Thanks for your patience as I researched this for you. We have come up
with some more background on the reasoning for Access ADP's using
Drop/Create for renaming UDF's rather than sp_Rename. As I suspected, it
was a tradeoff situation..

There are several bugs centered around the sp_Rename stored procedure,
whereby using this can be less than reliable because of incomplete data in
the SYSDEPENDS table. Indeed the source code for the sp_Rename procedure,
makes the following comment:

vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
-- WITH DEFERRED RESOLUTION, SYSDEPENDS IS NOT VERY ACCURATE, SO WE ALSO
-- RAISE THIS WARNING **UNCONDITIONALLY**, EVEN FOR NON-OBJECT RENAMES
raiserror(15477,-1,-1)

Caution: Changing any part of an object name could break scripts and stored
procedures.
The object was renamed to 'NewStoreSales'.

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Because SQL Server is unable to validate the dependencies for functions, a
conservative approach was taken by the Access developers which requires
the developer to drop and recreate the UDF. This forces the developer to
edit the source code for all dependent views and stored procedures and edit
them with the new function name.

The behavior whereby the Permissions and Extended Properties are also lost
when the Drop/Create happens is also posted as a bug for Access, but there
is no current indication of a plan or timeline for a fix. At the moment,
the only workaround is to recreate the Permissions and Extended Properties
after completing the rename procedure.

I do hope this helps, Malcolm. I know this isn't the answer you wanted to
hear, but I hope it sheds some light on the background behind the
conservative approach methodology used..


Best Regards

Wade Jackson MCSD
Microsoft Access Developer Support

 

[Malcolm Cook]

Hi Wade,

Excellent!


I can entirely appreciate the decision of the Access developers. Your
asnwer in fact is entirely the answer I want to hear...

Thank you very much for researching this for me. It increases my
understanding of the architecture of MS Access, SQL Server, and your
commitment to developer support.

I suppose I can research this a bit further myself, if I should want to use
sp_rename... I'm not sure I appreciate the limitation of sp_rename, but,
you need not research this any further for me... I know where the issue
lies.

Thanks again and regards,

Malcolm

 

[Wade Jackson [MSFT]]

Great! We'll close this one down then.
And thanks for the kind word!

Best Regards

Wade Jackson
Microsoft Access Developer Support.