使用SQL Server 2000 + ASP 出现诡异的问题，SOOOOOOOOS

[d_einstein]

不知道为什么，现在访问数据库真接用表名会出错。比如 "select * from table_name "会出这样的错误：

Microsoft OLE DB Provider for SQL Server 错误 '80040e37'
对象名 'table_name' 无效。

而在表名前加上用户名就好了。比如 " select * from userName.table_name "
就一点问题都没有。

有人碰到过同样的问题吗？

[playyuer]

连接的 login =???

[happydreamer]

用sa连接

Set Conn=Server.CreateObject("ADODB.Connection")

Conn.ConnectionString="driver={SQL Server}; server=(local);uid=sa;pwd=xxx;database=dbname"

Conn.Open

Set rs1=Server.CreateObject("ADODB.RecordSet")
Mysql = "Select * FROM from table_name where colname= '" & xxx & "'"
rs1.open MySql,Conn,1,3,1

[d_einstein]

拜托，我用不着把连接信息也写出来吧。 如果"select * from table_name" 不能做，那还能做什么？

[saucer]

the table is owned by "userName", if you just use "table_name", SQL Server first searches for "table_name" whose owner is the current user (you). If it
doesn't find one then it searches to see if there's one owned by DBO.
If it doesn't find one there, it will return a '80040e37" error(even though the table exists but is owned by someone else).

try to change the owner to dbo:

exec sp_changeobjectowner 'userName.table_name','dbo'

[saucer]

what if you run in your code

select current_user as 'username'

what did you get? is it same as the owner of the table?

[d_einstein]

你说的很有道理。
In fact.I'm the database creator and the table owner.And this is my default database. Everything was OK just one minute ago...I don't know what's wrong with my SQL Server.:(

[d_einstein]

It seems the first way that system search table name doesn't work any more.I think maybe there's a configuration but I can't find it.

[tangxc2003]

同意 happydreamer(小黑)

[pengdali]

操作步骤：
1、sa登陆
2、建立数据库test
3、建立登陆admin，默认数据库可以选择test（也可以其他，但必须有权限），服务器角色不要选，在数据库访问里选到test，数据库角色选上db_owner，保存。
4、在查询分析器里用admin登陆，建立表create table aaa (a char(1))。
5、这个时候select * from aaa就可以了。

[txlicenhe]

太正常了，只有数据表的所有者是dbo时才可以省略。

exec sp_changeobjectowner 'tablename','dbo'

[zjcxc]

表的所有者的问题.


查询分析器--用sa连接你的SQL--选择你的数据库为当前数据库--执行下面的语句--不要理会错误提示

--更改数据库中全部表的所有者
exec sp_msforeachtable 'exec sp_changeobjectowner ''?'',''dbo'''