[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 179
  • Last Modified:

How to reference a table without specifying database.owner?

Hi -

We've installed a new database on an existing SQL Server 2000 instance.  We're using this same database locally on the same version of SQL Server.  On our local site, we can reference the tables in the database simply by table name (select * from info).  However, on the new installation, we can only reference the tables by databasename.dbo.table_name (select * from mydb.dbo.info).  We've checked the permissions of the user set up in the ColdFusion Datasource on both installations and they appear to be the same (they're both DBO's of the databases).  We've got the databasename in the JDBC URL of the datasource also.  The ONLY difference that we can find between the two installations is that one is ColdFusion 6.1 and the other is ColdFusion 7 (not sure if that helps, just wanted to mention it).  

Any ideas on if this is a permissions problem and how to solve it or is there something we need to set up in ColdFusion?

Thanks!
0
rlavalle
Asked:
rlavalle
  • 4
  • 2
  • 2
2 Solutions
 
rlavalleAuthor Commented:
One thing to add.  We found if we didn't use the JDBC driver and instead selected 'Microsoft SQL Server' on the ColdFusion 7 installation, we don't have this issue.  However, using the JDBC driver and ColdFusion 6 does work.   What driver is the 'Microsoft SQL Server' selection using?  We've been dowloading and installing the Microsoft SQL Server 2000 driver for JDBC (version 2.2) - do we not need to do this any longer?  Are the native drivers JDBC or ODBC?

Thanks!
0
 
mrichmonCommented:
I have used (and been told it is better to use) the Microsoft SQL Server drivers.

I don't know why it worked before and not now, but I would recommend using the Microsoft SQL Server driver on both 6 and 7.
0
 
Jerry_PangCommented:
I encountered the same error before.
I created and populate the db using a different account say "Master"

so all my db have to be prefixed by Master.tablename

i fixed it by granting group permission (in sql interactive or query analyzer)
GRANT GROUP TO Master;
GRANT MEMBERSHIP IN GROUP Master TO dbo; // or any user you want

now when i logged in, i can now access using any select statement without the "dbo." prefix

problem is you might not be able to grant this to your hosting db..


here's a link to my problem before.
http://www.experts-exchange.com/Databases/Q_20981335.html
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
Jerry_PangCommented:
>>>(they're both DBO's of the databases).  
oops...

can you check who is the current user logged in?
0
 
rlavalleAuthor Commented:
The current user is the Coldfusion user.  I think we're not dealing with permissions on the database, but instead a difference in the way the SQL Server Driver for JDBC v2.2 is used in ColdFusion 6 and ColdFusion 7.  Is anyone using this driver with ColdFusion 7?  Is it necessary or is the default driver preferred?
0
 
mrichmonCommented:
Is it necessary --> Don't Know
or is the default driver preferred? --> Yes
0
 
Jerry_PangCommented:
>>reference the tables by databasename.dbo.table_name

in sql server, when user login, you can set the default database.
open enterprise manager.
open database>security>

right click on username, click properties.
specify the default database and the username.
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now