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 be rocket science to delete data older than 30 days?

Can it ?
is it this simple? Or is it supposed to > ?
DELETE FROM LOGRECS
WHERE datediff(d,getdate(), time)<=30Try:
DELETE FROM tbl
WHERE time_col <= DATEADD( d, -30, CURRENT_TIMESTAMP ) ;
Anith|||You're saying within the last 30 days. This statement might be more logical
to follow:
DELETE LogRecs
WHERE [time] < GETDATE() - 30;
or
DELETE LogRecs
WHERE [time] < DATEADD(DAY, -30, GETDATE());
"Kevini" <Kevini@.discussions.microsoft.com> wrote in message
news:5882D56F-7395-46A4-A49E-D0B096A47D8F@.microsoft.com...
> Can it ?
> is it this simple? Or is it supposed to > ?
> DELETE FROM LOGRECS
> WHERE datediff(d,getdate(), time)<=30|||Anith Sen wrote:
> Try:
> DELETE FROM tbl
> WHERE time_col <= DATEADD( d, -30, CURRENT_TIMESTAMP ) ;
> --
> Anith
When in doubt, don't delete.
SELECT MAX(time_col)
-- DELETE
FROM tbl
WHERE time_col <= DATEADD( d, -30, CURRENT_TIMESTAMP ) ;
Measure twice, cut once.|||I only want the last 30 days so nothing earlier than May 29 or should I say
records form today and back 30 days
"Aaron Bertrand [SQL Server MVP]" wrote:

> You're saying within the last 30 days. This statement might be more logic
al
> to follow:
> DELETE LogRecs
> WHERE [time] < GETDATE() - 30;
> or
> DELETE LogRecs
> WHERE [time] < DATEADD(DAY, -30, GETDATE());
>
>
> "Kevini" <Kevini@.discussions.microsoft.com> wrote in message
> news:5882D56F-7395-46A4-A49E-D0B096A47D8F@.microsoft.com...
>
>|||Kevini wrote:
> Can it ?
> is it this simple? Or is it supposed to > ?
> DELETE FROM LOGRECS
> WHERE datediff(d,getdate(), time)<=30
It helps to have the rocket pointed in the right direction... :-)
WHERE DATEDIFF(d, time, GETDATE()) > 30|||Thanks Guys...Aaron's worked beautifully. And the rocket pointed the right
way:-)
"Tracy McKibben" wrote:

> Kevini wrote:
> It helps to have the rocket pointed in the right direction... :-)
> WHERE DATEDIFF(d, time, GETDATE()) > 30
>|||Kevini wrote:
> Thanks Guys...Aaron's worked beautifully. And the rocket pointed the right
> way:-)
>
Houston, we have lift-off!|||>I only want the last 30 days
But your subject says "data older than 30 days"
If you want rows that are within the last 30 days, then
WHERE [time] > DATEADD(DAY, -30, GETDATE())
AND [time] <= GETDATE()|||Kevini wrote:
> Thanks Guys...Aaron's worked beautifully. And the rocket pointed the right
> way:-)
>
So, is it really a rocket, or are you just happy to see us ;)
/impslayer, aka Birger Johansson