Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
 Posted - 2008-10-08 : 13:59:15
|
| Let me explain the situation by giving two examples:Say I create two database handles in PHP, dbh1 and dbh2, which point to a particular database on the server (DatabaseA). Running the typical sql query in PHP (i.e. select * from some_table) works just fine in this scenario. There's no need for me to write select * from some_database.some_schema.some_table. So, that's good.Now, let's say I create a database handle in PHP which points to a certain database in sql. I have another database handle which points to a different database in sql. Let's call the handles the same as above (dbh1 and dbh2). And let's call the databases DatabaseA and DatabaseB. I also have two PHP functions (lets' call them fcn1 and fcn2) which query one database each. The first function queries DatabaseA using dbh1, and the second function queries DatabaseB using dbh1. The connoation is written out like above, i.e. Select * from some_table. I execute fcn1 and then fcn2. Almost forgot, the default schema here will be dbo. So...fcn1 and fcn2 look something like this:fcn1(){$dbh1->query("Select * From Some_Table_in_DatabaseA");}fcn2(){$dbh2->query("Select * From Some_Table_in_DatabaseB");}For some reason, I'm getting a sql error via PHP that it cannot find Some_Table_in_DatabaseB. What I did was prepend DatabaseB.dbo to Some_Table_in_DatabaseB in the query for fcn2. I reran fcn1 and fcn2, in that order, and it worked fine. The problem is I shouldn't have to do this to make it work. I opened up two different handles in PHP. It's as if SQL is only seeing one handle. Is this normal? Does SQL generally switch database contexts implicity? Thank you for any help in advance. |
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-10-08 : 14:36:45
|
| change PUBLIC permission to read |
 |
|
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2008-10-08 : 14:55:17
|
| Call me stupid but I'm not quite sure where to make this change or even why this affects database context, with respect to database handles (Not questioning your advice just curious as to why). If you can point me in the right direction to access said permission, I'd appreciate it. |
|
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-10-08 : 15:15:23
|
| GRANT SELECT ON DatabaseB.dbo.Some_Table_in_DatabaseB to public |
|
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-10-08 : 15:18:35
|
| or in Enterprise Manager right click on the table Some_Table_in_DatabaseB -> properties -> permissions highlight user "public" check SELECT box and [OK] |
|
|
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2008-10-15 : 16:05:31
|
| Tried it. It didn't seem to work. Just to clarify, as perhaps I didn't explain this very well, I create two database handles in PHP, each pointing to different databases. I do some stuff with the first database handle. Then I try to do some stuff with the second database handle, which fails because SQL still thinks the second database handle is pointing to the first database. Clearly, it sounds like a permissions issue.The loginname, let's call it the user JoeBuck, has free will to select, delete,update, and whatever else you can think of on both databases and at least all user-created tables in both databases. It's not like JoeBuck only can priviledges in the first database but not the second. All I know is I have to explicity point to the database in my server-side code in a database query, which defeats the purpose of creating two completely separate database handles.Perhaps it is a configuration issue with my php libaries and my IDE? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2008-10-16 : 02:24:08
|
| maybe the new_link parameter for the mssql_connect() can help.See this: https://trac.cakephp.org/ticket/3454WebfredPlanning replaces chance by mistake |
|
|
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2008-10-16 : 16:41:57
|
| Thanks! That fixed it. Thank you! Thank you! |
|
|
|
|
|
|
|
|
weird database handle issue in PHP