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

Can't Attach a Deleted Database

We accidentally deleted a development database from our SQL2K server. We don't have a valid backup (never made one because it was in development). I was able to recover the .mdf and .ldf files but SQL won't allow us to attach them (invalid files or page fault data). Everything seems to indicate that this can't be done but I'm hoping somebody out there has the solution. Thanks, Roni.Give the mdf file a try without the ldf file. But if you
don't have a backup and your mdf/ldf files are both
damaged, you are hosed.
I've heard there are so-called 'recovery experts' out
there. But I've never used them.
If there is a chance they'll come back asking for backups
and unless they've given you okay to not have any backups,
make regular backups regardless.
Linchi
>--Original Message--
>We accidentally deleted a development database from our
SQL2K server. We don't have a valid backup (never made
one because it was in development). I was able to recover
the .mdf and .ldf files but SQL won't allow us to attach
them (invalid files or page fault data). Everything seems
to indicate that this can't be done but I'm hoping
somebody out there has the solution. Thanks, Roni.
>.
>|||Hi Roni,
Unfortunately, there is no recommended method of recovering the database if
there was no backup. It seems that you recovered the MDF/LDF files and
tried to attach the database. I would like to know how you attached the
database? Could you tell me the detailed error message?
Please make ensure that the specified MDF file is correct. In the case, if
the MDF file is incorrect, it is hard to recover the database.
For additional information regarding attaching database via Enterprise
Manager, please refer to the article on the SQL Server Books Online.
Topic: "How to attach and detach a database (Enterprise Manager)"
In Query Analyzer, please try to use sp_attach_db stored procedure to
attach database. Do any error messages occur? If so please provide the
detailed error message.
If we still fail to attach the database, please try to use
sp_attach_single_db stored procedure. Do any error messages occur? If so,
please provide the detailed error message.
For addition information regarding these stored procedure, please refer to
the following articles on SQL Server Books Online.
Topic: "sp_attach_single_file_db"
Topic: "sp_attach_db"
Thanks for your understanding.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||I want to thank all of you, who took time to respond to this message. Shortly after I posted my inquiry, management authorized the $250 for a call to Product Support. We were unable to get the database back from the .mdf and .ldf files using any of the techniques, which were submitted. Having consulted with the highest levels of support, the consensus was that the file headers were corrupt (perhaps as part of the simple act of deletion), and were, therefore, unusable.
There was some talk about third-party recovery services, but it just appeared to be costly, require a lot of time and still may not work. We decided to go back to a 5-week old, valid backup and start work from there.
BTW, because MS wasn't able to fix the problem, I will be recieving a refund on the $250 charge.
Again, thank you all for your time and consideration.
Roni Hourihane|||Hi Roni,
Thanks for your feedback. It lets us know the status of this issue. Please
feel free to post in the group if you would like further assistance.
Thanks for using MSDN newsgroup again.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.sql

Cannot stop or start SQL server from SQLEM

Hi,
Merry Christmas to Everyone.
We have a SQL2k server that is not in any domain (in a
workgroup). I registered it in the Enterprise Manager on
my workstation with the sa account. However, I can do
anything but stop or start the SQL server and server
agent. Here is the error message:
An error 5 - (Access is denied) occurred while performing
this service operation on the SQLServerAgent service.
Any suggestions?
Thanks.
David WangServices are an OS thing, not an SQL thing. Log in as
admin.
>--Original Message--
>Hi,
>Merry Christmas to Everyone.
>We have a SQL2k server that is not in any domain (in a
>workgroup). I registered it in the Enterprise Manager on
>my workstation with the sa account. However, I can do
>anything but stop or start the SQL server and server
>agent. Here is the error message:
>An error 5 - (Access is denied) occurred while performing
>this service operation on the SQLServerAgent service.
>Any suggestions?
>Thanks.
>David Wang
>.
>|||Yes, you are right. I have no problem to do this if I log
in from the local server. I am trying to do this from a
remote machine. I thought the sa account could impersonate
the local system admin role.
Thanks.
>--Original Message--
>Services are an OS thing, not an SQL thing. Log in as
>admin.
>>--Original Message--
>>Hi,
>>Merry Christmas to Everyone.
>>We have a SQL2k server that is not in any domain (in a
>>workgroup). I registered it in the Enterprise Manager on
>>my workstation with the sa account. However, I can do
>>anything but stop or start the SQL server and server
>>agent. Here is the error message:
>>An error 5 - (Access is denied) occurred while
performing
>>this service operation on the SQLServerAgent service.
>>Any suggestions?
>>Thanks.
>>David Wang
>>.
>.
>|||sa is not an administrator to the operating system.