mssql db problem

grahamor

Hi,

I recently moved a ms sql database from one host to another.

The tables, sprocs etc. are all saved like username.tablename so when i am logged in as username i can call a table directly like

select * from tablename

With the new host my db login is different and i cannot call a table directly like above, i need to call it like

select * from [B]oldusername[/B].tablename

Is there a way i can call a table directly with a different login instead of having to change my code or rename all the DB objects ?

Thanks in advance.

Ciaran187

Best practice is to fully qualify names like this. I know that's not much help and I don't know anyone that dos this so...

It looks like "oldusername" is the name of the database and tablename is obviously the table name. Use phpMyAdmin or the like and inspect your database thoroughly. Make sure you don't just need to rename the database.

Sorry if this doesn't help much.

seamus

It depends on the version of mssql. If its 2005 or better, then you can set the default schema of newusername to oldusername. On the other hand if it's 2000 or less, you're pretty much stuck with renaming option.

TinCool

Checkout sp_changeObjectOwner.

So, to change the owner of say Customer to dbo run

EXEC sp_ChangeObjectOwner, 'Customer', 'dbo'

You could run something like the below to create the executable commands

SELECT 'EXEC sp_ChangeObjectOwner ' + CHAR(39) + name + CHAR(39) + ', ' + CHAR(39) + dbo + CHAR(39) from sysobjects where xtype IN('U','P')

This is just off the top of my head.