| Author |
 Topic  |
|
Astralis
Senior Member
USA
1218 Posts |
 Posted - 24 May 2008 : 05:28:58
|
I've noticed this is happening to a couple of people and it's happening to me.
I took a .bak of a SQL Server 2000 file and moved it onto SQL Server 2005 Express and received the following error:
Microsoft OLE DB Provider for SQL Server error '80040e37'
Invalid object name 'FORUM_MEMBERS'.
/forum/inc_func_common.asp, line 962
I know the table is there.
What is the real issue that's causing this?< |
Edited by - Astralis on 27 May 2008 01:00:41 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Astralis
Senior Member
USA
1218 Posts |
Posted - 24 May 2008 : 05:45:53
|
Yes. I just noticed this. I also see that if you try to change the schema of a user, it won't let you.
What happens if you want to connect to a site and access tables created under different schema? For example, maybe an application uses dbo.table1 and astralis.table2. How do you apply two different schemas to a single user in a connection string?< |
 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 24 May 2008 : 05:50:03
|
You use can always use the schemaName.tableName syntax in the SQL. So, you're dbo in one and not in the other? If you control de 2nd server, you can always try to assign your accounr a default db_owner role and see if that helps.
P.S.: You can also configure the user to own the db_owner schema as well. I can't reproduce the exact situation, so I'm not sure what will actually fix it.< |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Astralis
Senior Member
USA
1218 Posts |
Posted - 24 May 2008 : 05:56:12
|
I don't understand. I have both astralis.tablename and dbo.tablename2 on the same server in the same database. There are no tables that have the same name.
When I connect, how can I get a user to be able to access both or all schemas?< |
Edited by - Astralis on 24 May 2008 05:57:22 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 24 May 2008 : 05:59:32
|
Which schema has the correct data? dbo or astralis? Find the one that has the data that matters to you and define that as the default schema for your user.
The tables do have the same name, they were just created in different schemas.
< |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Astralis
Senior Member
USA
1218 Posts |
Posted - 24 May 2008 : 06:04:04
|
That's the problem, I have tables that I need on the same page that use different schema. If I apply the astralis schema to the user, it will run the astralis tables but it won't run the dbo tables that I need as well. I need to run both schemas at the same time on the same page, especially for some of the syndication of the forum content I do across my sites.
Also, when I try to apply the astralis schema to the user, I save it and go back and see that it has the dbo schema applied to it instead. This is very odd.< |
Edited by - Astralis on 24 May 2008 06:08:29 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 24 May 2008 : 06:13:53
|
The best thing would be to get all the tables into the same schema, with an ALTER SCHEMA statement:
ALTER SCHEMA dbo TRANSFER astralis.FORUM_MEMBERS
This would move the table FORUM_MEMBERS from the astralis schema into the dbo schema.< |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Astralis
Senior Member
USA
1218 Posts |
Posted - 24 May 2008 : 06:21:50
|
First, thanks for helping with this.
I'm so new to having my own SQL Server on my own server (even though it is Express). Where do I run that schema alteration?
Also, when I found a user that had the astralis schema associated with it, I ran it and got this error. Is this related to the problem or is it something different you recognize?
The SELECT permission was denied on the object 'FORUM_MEMBERS', database 'sfdbnews2005', schema 'astralis'. < |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 24 May 2008 : 06:30:03
|
You need to open a query window and choose the database you are using from the dropdown at the top left... which you seem to have done by now...
The account you're connecting with, what server roles has it assigned?< |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Astralis
Senior Member
USA
1218 Posts |
Posted - 24 May 2008 : 06:33:49
|
quote:
The account you're connecting with, what server roles has it assigned?
Ah...I see. It has "public" assigned to it as a server role. I guess I need to go and mark everything, right?
To confirm, this alter script will change the astralis schema to dbo? Is there a way to do this automatically for every table? Like, astralis.* ?
ALTER SCHEMA dbo TRANSFER astralis.FORUM_MEMBERS
< |
Edited by - Astralis on 24 May 2008 06:37:01 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 24 May 2008 : 06:35:20
|
Just sysadmin or securityadmin will cut it to change object ownership. You should remove those once you're done.
P.S.: I'm talking about server roles, you get info about those in the security folder for the server, not any specific database. If using database roles, db_securityadmin should be enough.< |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 24 May 2008 : 06:38:44
|
quote:
Originally posted by Astralis
quote:
The account you're connecting with, what server roles has it assigned?
Ah...I see. It has "public" assigned to it as a server role. I guess I need to go and mark everything, right?
To confirm, this alter script will change the astralis schema to dbo? Is there a way to do this automatically for every table? Like, astralis.* ?
ALTER SCHEMA dbo TRANSFER astralis.FORUM_MEMBERS
Probably a script could be written, but it will be quicker to write a similar statement for all tables you need to change.< |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Astralis
Senior Member
USA
1218 Posts |
Posted - 24 May 2008 : 06:45:53
|
quote:
Just sysadmin or securityadmin will cut it to change object ownership. You should remove those once you're done.
What do you mean by "remove those"? If I understand you correctly, you're saying to remove that server role from the user. But if I do that, then I won't be able to open the tables, even when the schema matches. I'm sure I don't understand what you're saying. =)< |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 24 May 2008 : 06:48:33
|
| That's wrong. You need no server roles to access tables, that is determined by the database roles, not the server roles. Even accessing tables, you don't even need db_owner role, you can settle with "lesser" roles.< |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Astralis
Senior Member
USA
1218 Posts |
Posted - 24 May 2008 : 06:49:56
|
quote:
Probably a script could be written, but it will be quicker to write a similar statement for all tables you need to change.
I have 200 tables. I'm not sure how quick that will take.< |
|
|
|
Topic |
|
Topic Locked
