August 8, 2003 at 12:38 pm
I have a database where all the Tables are owned by a Generic SQL User who has db_owner priviliges. I wanted to run the script "Selective Defrag/Reindex and Log" (which I found in the script files) against this database. When I run the script I get the following error:
Server: Msg 2501, Level 16, State 45, Line 1
Could not find a table or object named 'LOTUS'. Check sysobjects.
During my research of the problem I noticed that tables which belong to dbo have no problem but tables which belong to a "User" generate the above error. I ran the script as Sysadmin but the owner of the table only has Public and db_owner set.
Now down to my real question, can I change all the tables to belong to dbo without any consequences. There is an application that has been written against this database and I am afraid if I change the owner of the tables to dbo it will impact the application. I did notice that all the Stored Proc did in fact belong to dbo...Any advice/info would be greatly appreciated. Thanks in advance...
August 8, 2003 at 9:39 pm
You need to review the application to see whether it refers objects with owner name. If it does, you will have problem after changing the owner to dbo without modifying the application.
It seems changing script "Selective Defrag/Reindex and Log" will be much easier.
August 10, 2003 at 7:40 am
No easy way to find out that I can think of. If they were lazy and didnt fully qualify the table names - just doing select * from lotus instead of select * from andy.lotus, AND they are no other objects with the same name, you could change the owner and all would be well. If they fully qualified or you have name collisions, you'd have to change their code. I have some notes I wrote up a while back:
http://www.sqlservercentral.com/columnists/awarren/worstpracticesobjectsnotownedbydbo.asp
All in all, changing the script is easier as Allen indicated, but long term you might be better off to just change them all to DBO and be done with it.
Andy
August 11, 2003 at 5:59 am
Thanks to all who responded. I will see if I can get a copy of the application and see if tables are referenced via owner.table... again thanks for help Reg
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply