October 4, 2002 at 12:41 am
IN MY DATABASES THERE ARE SOME TABLES CREATED BY A USER(FICUSER) IF DO
SELECT * FROM SYSOBJECTS WHERE NAME = 'FCT_ALL_TXN_T' AND XTYPE='U'
IT SHOWS THE TABLE BUT WHEN I TRY TO RUN
EXEC SP_CHANGEOBJECTOWNER 'FICUSER.FCT_ALL_TXN_T', 'DBO'
IT RETURNS THIS ERROR MESSAGE
SERVER: MSG 15001, LEVEL 16, STATE 1, PROCEDURE SP_CHANGEOBJECTOWNER, LINE 38
OBJECT 'FICUSER.FCT_ALL_TXN_T' DOES NOT EXIST OR IS NOT A VALID OBJECT FOR THIS OPERATION.
I CHECKED IN SQL ENTERPRISE MANAGER IT SHOWS THIS TABLE AS A SYSTEM TABLES AND DOSN'T EVEN ALLOW ME TO DROP THIS TABLE
IN THIS PROCESS I AM LOGGED ON AS DBO OF THE DATABASE
ANY WAY TO DROP THESE TABLE OR CHANGE THE TYPE TO 'USER'
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
October 4, 2002 at 5:55 am
To the best of my knowledge, if they used sp_MSmarksystem to make it a system table or altering the status of the table in the sysobjects table will be an issue trying to unmark. The best thing to do is create a new table move the data to it, drop the bad table with DROP TABLE and rename the new table with the old name.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 4, 2002 at 7:17 am
Thanks Antares,
Whai I am interested in is to know why it happened and how to change it back to user type
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
October 4, 2002 at 10:01 am
You may never know the "why". If someone did this, you would have to ask them.
To get them back, I think you could drop this as "sa". Maybe need to enable updates to system tables first.
Steve Jones
October 4, 2002 at 10:51 am
go to the table sysobjects and search for the table in question. make sure you allow modifications to sys tables and then change the column xtype from 'S' to 'U'. Make sure you use upper case letters.
Robert Marda
SQL Server will deliver its data any way you want it
when you give your SQL Programmer enough developing time.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
October 4, 2002 at 6:14 pm
It is not based on xtype but instead status column. When you use sp_MSmarksystem to make the table a system table you will find the only column updated is status and xtype will remain U, which would explain why you can see it with that xtype. Now when it changes to a system table to ORs the value 0xC000000 to the current status value. I tried to set back but found it did not in some cases return to normal in the listing and odd things happened to those tables. The safest way to make sure your DB is fully protected is as I stated and create a new table then import the data, drop the original table and rename the new table you created. The reason it can cause issues is that some information other information to denote things like the existance of triggers on the table are part of the status. If anyone can safely OR out the 0xC00000 value then let me know as I am just curious.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply