SQL Server table owners question

grahamor · 27-03-2014 10:16am #1

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 · 27-03-2014 3:07pm

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 · 27-03-2014 5:07pm

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 · 28-03-2014 10:46pm

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 · 31-03-2014 3:39pm

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

Cheers

SQL Server table owners question

Comments