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

Cant access table due to no owner (I think)

I can't access a number of tables on my SQL Server 7.0 database. Have checked using Enterprise Manager and the table does not appear to have any owner. When trying to do anything with the table I always get the following message

{SQL-DMO} The name 'Table Name' was not found in the tables collection. If the name is a qualified name, use [] to seperate various parts of the name, and the try again.

Have tried to change the owner by using sp_changeobjectowner but I am either getting the syntax wrong or it ain't working.

Any ideas??

c8lWhen you look in Enterprise Manager, what do you see?|||Hi,

The only thing I see is the table name (TABLE1), the type (USER) and the creation date. The owner column is blank. Normally you would expect it to say dbo.|||Blank?

What happends when you run this in QA?

SELECT * FROM INFORMATION_SCHEMA.Tables

Maybe you need to close EM and relaunch it. ...|||Thanks for the help but the problem has been resolved, I had to manually update the sysobject table so that the UID for each of the objects that could not be accessed was set to 1. For some reason this was 6.

Cheers
c8l

Cannot Truncate due to Fks

i read that i can Truncate a table even if child table has no records so i tries to disable constraints but still can't get it to work

Cannot truncate table 'InventoryPC' because it is being referenced by a FOREIGN KEY constraint.

disabling code on lines 9-13 and enabling codes on 36-40

1ALTER PROCEDURE dbo.RevertDB23/* Reverts Database to original "Clean" State */4AS5SET NOCOUNT OFF6DECLARE @.LogAS varchar(MAX), @.RowsInDBAS int7SET @.Log ='RevertDB Started at ' +CAST(GETDATE()AS varchar(50)) +'\n'89/* *** Disable Constraints *** */10ALTER TABLE BookingNOCHECK CONSTRAINTALL11ALTER TABLE InventoryPCNOCHECK CONSTRAINTALL12ALTER TABLE PCNOCHECK CONSTRAINTALL13ALTER TABLE PlatformNOCHECK CONSTRAINTALL1415/* *** Start Truncates *** */16TRUNCATE TABLE Booking17SET @.Log = @.Log +'Trucate Table Booking - Done' +'\n'18SET @.RowsInDB = (SELECTCOUNT(BookingID)FROM Booking)19SET @.Log = @.Log +'-- Rows Affected: ' + CAST(@.@.ROWCOUNT AS varchar(10)) + ', Rows in Table: ' + CAST(@.RowsInDB AS varchar(10)) + '\n'2021TRUNCATE TABLE InventoryPC22SET @.Log = @.Log + 'TrucateTable InventoryPC - Done' + '\n'23SET @.RowsInDB = (SELECT COUNT(InventoryID) FROM InventoryPC)24SET @.Log = @.Log + '-- Rows Affected: ' + CAST(@.@.ROWCOUNT AS varchar(10)) + ', Rows in Table: ' + CAST(@.RowsInDB AS varchar(10)) + '\n'2526TRUNCATE TABLE PC27SET @.Log = @.Log +'Trucate Table PC - Done' +'\n'28SET @.RowsInDB = (SELECTCOUNT(PCID)FROM PC)29SET @.Log = @.Log +'-- Rows Affected: ' + CAST(@.@.ROWCOUNT AS varchar(10)) + ', Rows in Table: ' + CAST(@.RowsInDB AS varchar(10)) + '\n'3031TRUNCATE TABLE Platform32SET @.Log = @.Log + 'TrucateTable Platform - Done' + '\n'33SET @.RowsInDB = (SELECT COUNT(PlatformID) FROM Platform)34SET @.Log = @.Log + '-- Rows Affected: ' + CAST(@.@.ROWCOUNT AS varchar) + ', Rows in Table: ' + CAST(@.RowsInDB AS varchar(10)) + '\n'3536/* *** Enable Constraints *** */37ALTER TABLE BookingWITH CHECK CHECK CONSTRAINTALL38ALTER TABLE InventoryPCWITH CHECK CHECK CONSTRAINTALL39ALTER TABLE PCWITH CHECK CHECK CONSTRAINTALL40ALTER TABLE PlatformWITH CHECK CHECK CONSTRAINTALL4142SET @.Log = @.Log +'*** End Truncates ***' +'\n'43/* *** End Truncates *** */4445/* *** Start Insert Platform *** */46SET @.Log = @.Log +'Start Insert Platform' +'\n'4748EXEC dbo.InsertPlatform'Windows XP SP2 Professional Edition','Some description for Windows XP SP2 Professional Edition over here …'49EXEC dbo.InsertPlatform'Windows Vista Ultimate','See everything you''re working on more clearly with Windows Aero, and quickly switch between windows or tasks using Windows Flip 3D and Live Thumbnails. You can easily find what you need—when you need it―with Instant Search and live icon previews that display the actual contents of your files. And while you''re at it, give your personal productivity a boost with instant access to the information you care about using Windows Sidebar and Gadgets. Put these easy-to-use and customizable mini-applications on your desktop and reveal the information you''re looking for at a glance.\nWebsite: http://www.microsoft.com/windows/products/windowsvista/seeit/default.mspx'50EXEC dbo.InsertPlatform'Apple Mac OS X Tiger','Some description for Apple Mac OS X Tiger over here …'51EXEC dbo.InsertPlatform'Apple Mac OS X Leopard','Desktop: The new look of Leopard showcases your favorite desktop image and puts new file Stacks at your fingertips for a stunning, clutter-free workspace.\nFinder: Browse your files like you browse your music with Cover Flow.\nTime Machine: See how your system looked on any given day and restore files with a\nWebsite: http://www.apple.com/macosx/leopard/features/'52EXEC dbo.InsertPlatform'Red Hat Linux','Some description for Red Hat Linux over here …'5354SET @.Log = @.Log +'Rows In Platform: ' + (SELECTCOUNT(PlatformID)FROM Platform) +'\n'55/* *** Start Insert PC *** */56SET @.Log = @.Log +'Start Insert PC' +'\n'5758DECLARE @.WinXPint, @.WinVistaint, @.OSXTigerint, @.OSXLeopardint, @.RedHatint59SET @.WinXP = (SELECT PlatformIDFROM PlatformWHERE Title ='Windows XP SP2 Professional Edition')60SET @.WinVista = (SELECT PlatformIDFROM PlatformWHERE Title ='Windows Vista Ultimate')61SET @.OSXTiger = (SELECT PlatformIDFROM PlatformWHERE Title ='Apple Mac OS X Tiger')62SET @.OSXLeopard = (SELECT PlatformIDFROM PlatformWHERE Title ='Apple Mac OS X Leopard')63SET @.RedHat = (SELECT PlatformIDFROM PlatformWHERE Title ='Red Hat Linux')6465EXEC dbo.InsertPC'Fusion PC One','Description here ...','Intel Core2 Duo E6600 2.4 GHz 1066MHz','1GB Dual Channel DDR2 667 SDRAM','120GB SATA2 NCQ HDD','NVIDIA GeForce 8600 256MB GDDR3','22" 3000:1 Wide Screen LCD', @.WinXP66EXEC dbo.InsertPC'Fusion PC Two','Description here ...','Intel Core2 Duo E6850 3 GHz 1333MHz','2GB Dual Channel DDR2 800 SDRAM','240GB SATA2 NCQ HDD','NVIDIA GeForce 8800 Ultra 256MB GDDR3 SLI','24" 3000:1 Wide Screen LCD', @.WinVista67EXEC dbo.InsertPC'Fusion PC Three','Description here ...','AMD Athlon 64 X2 Dual Core 6000+ 3 GHz','2GB Dual Channel DDR2 667 SDRAM','240GB SATA2 NCQ HDD','ATI Radeon Cross Fire 2900 256MB GDDR3','24" 3000:1 Wide Screen LCD', @.WinVista68EXEC dbo.InsertPC'Fusion X1','Description here ...','Intel Core2 Extreme Q6850 3 GHz 1333MHz','6GB Dual Channel DDR2 800 SDRAM','500GB SATA2 NCQ HDD','NVIDIA GeForce 8800 Ultra 256MB GDDR3 SLI','30" 3000:1 Wide Screen LCD', @.OSXLeopard69EXEC dbo.InsertPC'Fusion X2','Description here ...','AMD Athlon 64 FX 74 3 GHz','6GB Dual Channel DDR2 800 SDRAM','500GB SATA2 NCQ HDD','NVIDIA GeForce 8900 Ultra SLI 256MB GDDR3','30" 3000:1 Wide Screen LCD', @.WinVista70EXEC dbo.InsertPC'Fusion Tiger 1','Description here ...','Intel Core2 Duo E6600 2.4 GHz 1066MHz','2GB Dual Channel DDR2 800 SDRAM','120GB SATA2 NCQ HDD','NVIDIA GeForce 8600 256MB GDDR3 SLI','22" 3000:1 Wide Screen LCD', @.OSXTiger71EXEC dbo.InsertPC'Fusion Linux 1','Description here ...','AMD Athlon 64 X2 6000+ 3 GHz','1GB Dual Channel DDR2 800 SDRAM','120GB SATA2 NCQ HDD','NVIDIA GeForce 8600 256MB GDDR3','22" 3000:1 Wide Screen LCD', @.RedHat7273SET @.Log = @.Log +'Rows In PC: ' + (SELECTCOUNT(PCID)FROM PC) +'\n'7475/* *** Start Insert Inventory *** */76SET @.Log = @.Log +'Start Insert Inventory' +'\n'7778DECLARE @.F1int, @.F2int, @.F3int, @.FX1int, @.FX2int, @.FT1int, @.FR1int79SET @.F1 = (SELECT PCIDFROM PCWHERE Title ='Fusion PC One')80SET @.F2 = (SELECT PCIDFROM PCWHERE Title ='Fusion PC Two')81SET @.F3 = (SELECT PCIDFROM PCWHERE Title ='Fusion PC Three')82SET @.FX1 = (SELECT PCIDFROM PCWHERE Title ='Fusion X1')83SET @.FX2 = (SELECT PCIDFROM PCWHERE Title ='Fusion X2')84SET @.FT1 = (SELECT PCIDFROM PCWHERE Title ='Fusion Tiger One')85SET @.FR1 = (SELECT PCIDFROM PCWHERE Title ='Fusion Linux One')8687EXEC dbo.InsertInventory 10, @.F1, 2.5,'iCluster Fusion One'88EXEC dbo.InsertInventory 10, @.F2, 2.5,'iCluster Fusion Two'89EXEC dbo.InsertInventory 10, @.F3, 2.5,'iCluster Fusion Three'90EXEC dbo.InsertInventory 6, @.FX1, 6,'iCluster Fusion X1'91EXEC dbo.InsertInventory 6, @.FX2, 6,'iCluster Fusion X2'92EXEC dbo.InsertInventory 10, @.FT1, 3,'iCluster Fusion Tiger One'93EXEC dbo.InsertInventory 30, @.FR1, 2,'iCluster Fusion Linux One'9495SET @.Log = @.Log +'Rows In Inventory: ' + (SELECTCOUNT(InventoryID)FROM InventoryPC) +'\n'9697RETURN @.Log98

When the Forigen Keys are exist the Truncate statement will not be executed on the master table, even it is disabled.

You have 2 options,

Option 1: Use Delete From <table name> & disabled constraint

Option 2: Drop the FK on the child table, use the truncate statement then recreate the FK on your child table.