April 6, 2009 at 7:09 am
Good day,
I think this problem started happening after a database was moved to another server. The user of the database (dbuser) was deleted and then recreated. Some of the tables were still assigned to the dbuser, and it is these tables I am having a problem with. The application that is accessing the dataabse is not able to access these tables. I can open them and view the data without a problem, but I am not able to change their ownership over to the dbo user. I get an error saying either the object does not exist or sp_changeobjectowner can not be used for this.
What I have been doing is renaming these tables and then running the SQL to recreate them and then copying the data over to these newly created tables.
I am relatively new to SQL, so I was looking for a better / quicker way. Is there a way I can just make a copy of these problem tables? then I could just manually rename the bad ones.
Thank you!
April 7, 2009 at 11:25 am
Due to the lack of response, here's some more information regarding this problem:
The actual error message I get when trying to use the sPchangeobjectowner procedure is "Server: Msg 15001, Level 16, State 1, procedure sp_changeobjectowner, Line 38" - Object 'table_name' does not exist or is not a valid object for this operation.
The same error arises if I fully qualiify the table_name with the user_name and run the SQL sp.
Another test I tried was a SELECT * INTO newtablename FROM tablename. This resulted in the error "Invalid object name 'tablename'"
Hope this additional info helps!
Cheers!
April 8, 2009 at 2:42 am
This could be a schema issue. One thing you should do is read what Books Online (BOL) has to say about schemas.
If the dbuser account in the old database was linked to the dbo schema, then any SQL issued by that account in the form SELECT col1 FROM table1 would be resolved by SQL Server as SELECT col1 FROM dbo.table1
In the new database, if you just creaed the account and did not specify a parent schema, then SQL will create a schema that has the same name as the account. The SQL statement would not get resolved as SELECT col1 FROM dbuser.table1
If you have now created tables that the account can access, you may find you have some tables that belong to schema dbo and some that belong to dbuser.
Also, when you run the spChangeObjectOwner and do not specify a schema, the command will use the default schema for your account (always dbo for a sysadmin user) and if the table is in the dbuser schema it will give a message that the table is not found.
If you do have a schema issue, then you need to decide which schema you want things to use, and set the authorities needed for the accounts to use the schema and rename the tables so thay all belong to the schema.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply