Can't change permissions on user-defined function (SQL2K)

Fresh install of W2K and SQL Server 2000 w/ Service Pack 3. Due to
security reasons, I'm supposed to remove a whole plethora of
permissions.
So I go into Enterprise Manager, Master table, "User-Defined
Functions", and right-click on one of the functions. I'm supposed to
remove the "Select" permission for "Public" from a function named
"fn_list_extendedproperty".
So I find the function, right-click it, choose "Properties", and get
the following error:
"Microsoft SQL-DMO
Error 0: [SQL-DMO]Unable to retrieve the text from
UserDefinedFunction object
'system_function_schema.fn_list_extendedproperty'."
Not a big deal, so I think. This happened on another box before and I
just removed the permissions successfully anyway. But this time, it
won't let me remove the permissions. I get the following error:
"Microsoft SQL-DMO (ODBC SQLState 42S02) [Note: "s" may be a "5"]
Error 208: Invalid object name
'system_function_schema.fn_list_extendedproperty'
Any idea what's going on here?
I found this thread on Google, but I'm not sure it applies:
http://groups.google.com/groups?q=%2B%22SQL+Server%22+%2B%22Unable+to+retrieve+the+text%22&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=u0Z0STHnCHA.2364%40TK2MSFTNGP12&rnum=1
(BTW, the owner is 'system_function_schema', not 'dbo'... is that
significant?)
Also, I have Admin rights on both the local machine and within SQL
Server.
Please advise. Thank you.Hi Doug,
The article applies in this situation. We cannot view system user-defined
functions using Enterprise Manager. According to my test, changing
permissions in Enterprise Manager also encounters the same error. As far as
the functions feature is concerned, THERE IS NO SUCH SCHEMA AS
"system_function_schema". When using SYSTEM functions, the syntax is to
prefix the (single-part!) name with two colons, as:
select * from ::fn_listextendedproperty(NULL, 'user', 'dbo','table',
'authors', NULL, NULL)
I think we need to change syspermissions table directly if we want to
change permissions for system user-defined functions currently.
Bill Cheng
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--
| From: Doug <None@.hotmail.com>
| Newsgroups: microsoft.public.sqlserver.server
| Subject: Can't change permissions on user-defined function (SQL2K)
| Organization: No
| Reply-To: Nothanks
| Message-ID: <4722jv075dgkf36blujp8uo958m18262kt@.4ax.com>
| X-Newsreader: Forte Agent 1.93/32.576 English (American)
| MIME-Version: 1.0
| Content-Type: text/plain; charset=us-ascii
| Content-Transfer-Encoding: 7bit
| Lines: 39
| Date: Wed, 06 Aug 2003 13:58:04 GMT
| NNTP-Posting-Host: 141.158.179.85
| X-Complaints-To: abuse@.verizon.net
| X-Trace: nwrdny03.gnilink.net 1060178284 141.158.179.85 (Wed, 06 Aug 2003
09:58:04 EDT)
| NNTP-Posting-Date: Wed, 06 Aug 2003 09:58:04 EDT
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onlin
e.de!news-lei1.dfn.de!news-fra1.dfn.de!npeer.de.kpn-eurorings.net!news-out.n
uthinbutnews.com!propagator2-sterling!news-in-sterling.nuthinbutnews.com!cyc
lone1.gnilink.net!spamkiller2.gnilink.net!nwrdny03.gnilink.net.POSTED!53ab27
50!not-for-mail
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:299768
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Fresh install of W2K and SQL Server 2000 w/ Service Pack 3. Due to
| security reasons, I'm supposed to remove a whole plethora of
| permissions.
|
| So I go into Enterprise Manager, Master table, "User-Defined
| Functions", and right-click on one of the functions. I'm supposed to
| remove the "Select" permission for "Public" from a function named
| "fn_list_extendedproperty".
|
| So I find the function, right-click it, choose "Properties", and get
| the following error:
|
| "Microsoft SQL-DMO
| Error 0: [SQL-DMO]Unable to retrieve the text from
| UserDefinedFunction object
| 'system_function_schema.fn_list_extendedproperty'."
|
| Not a big deal, so I think. This happened on another box before and I
| just removed the permissions successfully anyway. But this time, it
| won't let me remove the permissions. I get the following error:
|
| "Microsoft SQL-DMO (ODBC SQLState 42S02) [Note: "s" may be a "5"]
| Error 208: Invalid object name
| 'system_function_schema.fn_list_extendedproperty'
|
| Any idea what's going on here?
|
| I found this thread on Google, but I'm not sure it applies:
|
|
http://groups.google.com/groups?q=%2B%22SQL+Server%22+%2B%22Unable+to+retrie
ve+the+text%22&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=u0Z0STHnCHA.2364%40TK2MSFTNG
P12&rnum=1
|
| (BTW, the owner is 'system_function_schema', not 'dbo'... is that
| significant?)
|
| Also, I have Admin rights on both the local machine and within SQL
| Server.
|
| Please advise. Thank you.
|
|sql

Cannot update replication once live - have to recreate!

I have set up a transactional replication from SQL2000 (W2K) to SQL2005
(W2K3). Once it's running, if I add a new field to the publisher, it doesn't
appear in the subscriber tables. I have tried everything including
re-inititalising the job, stopping and starting agents - nothing works. All I
am left with is deleting then recreating the entire replication job which is
time consuming and annoying.
Somebody out there must have the answer to me inadequacy!
Thanks in advance,
Will, Leeds UK
Will,
I need to understand how you did this. How did you add the new field? Did
you use sp_repladdcolumn?
Also, when you say reinitializing the job I'm not sure what you mean. Do you
mean restart the snapshot agent? If so, this is not reinitialization. If the
subscription is marked for reinitialization, this is something different.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi, basically we replicate live databases onto a separate server for
reporting. Occasionally we modify the live database (add a new field say) and
need the replication job to reflect this change. At the moment, every time we
make a change we have to remove replication, delete the reporting database
copy, then re-add replication. The live databases are on SQL 2000 and
reporting copies on SQL2005.
I haven't tried sp_repladdcolumn yet. I have just been using various actions
from SQL Management Studio.
I am a little confused as to the 'official method' to achieve what I want!
What I do know is that our current method works BUT is very tedious...
Thanks
Will
"Paul Ibison" wrote:

> Will,
> I need to understand how you did this. How did you add the new field? Did
> you use sp_repladdcolumn?
> Also, when you say reinitializing the job I'm not sure what you mean. Do you
> mean restart the snapshot agent? If so, this is not reinitialization. If the
> subscription is marked for reinitialization, this is something different.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>