SQL Server table owners question

grahamor

Hi,

I'm moving a website to different hosting for a client. It uses classic ASP and SQL Server.

I created a user u1023117_d. Selecting from tables prefixed with dbo. works fine but some tables are prefixed with d_ie. and i cannot select from these unless i include the d_ie. prefix

select * from table (that's named dbo.table) works fine

But

select * from table2 (that's named d_ie.table) does not work (Invalid object name 'table2')

The problem is that all the code uses select statements directly in the code without any table prefix. The old user was d_ie so tables prefixed with this were working when selected without the prefix.

Does anyone know how i can set the database so my new user can call all tables irrespective of their prefix. I'm on shared hosting so my permissions might be limited.

Thanks in advance

amen

You could try the grant schema option. http://technet.microsoft.com/en-us/library/ms187940(v=sql.105).aspx

Why did you go the Schema route?

grahamor

Thanks.

It's a database from some eCommerce solution and looks like it's been tweaked over the years. I have not been involved in the development of the database or website.

Ill check out the link

Boskowski

Can the tables be moved to dbo? It is seldom a good idea to have tables or any other SQL objects for that matter in a user schema.

grahamor

Thanks, i managed to setup a copy locally, transfer all the non dbo tables back to dbo by using the alter schema command.

Cheers