Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
epaisley
Starting Member
4 Posts |
 Posted - 2007-01-26 : 10:05:48
|
| We have recently copied a database from one machine to another. On the old machine, when we access the tables we do not need to use the username.tablename convention to query them. On the new box we do. For example, to query a table called Page we would nee to do this on the new box.SELECT *FROM webdev.pageunfortunately all the code is written, without the username prefix. Is there a way to not use the username prefix?Thank you for your help...sorry for the newbie type question. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-01-26 : 11:44:48
|
| v2000 I presumesee sp_changeobjectowner - you can use it to change the owner of all the objects.How did you copy the database?If it was via dts or something similar then you were connectnig to the destination as webdev.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
epaisley
Starting Member
4 Posts |
Posted - 2007-01-29 : 09:42:13
|
| Thanks for your help. A little tedious going through changing all the tables, but it worked! Is there a way to set the owner when you copy the tables or objects? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-29 : 09:55:36
|
I would execute a query like this:SELECT 'EXEC sp_changeobjectowner ''' + TABLE_NAME + ''', ''dbo'''FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA = 'webdev'ORDER BY TABLE_NAME and then hand-edit the script, if necessary, and run the result.We create all objects owned by "dbo" so that they are universally available. That seems to be pretty much the norm IME - which kinda means that the Owner is redundant though!Kristen |
|
|
|
|
|
|
Database Tables and User Names