Properly handle the user-bound schema

We hardcode 'dbo' only in one place, so it should be an easy fix. We just need to figure out what is the proper way to get the schema of the current user. Patches welcome!

Can you easily pull the DefSchemaName for the user that Drupal is logging in with?

Eg: EXEC sp_helpuser 'DrupalUser'

...additionally, by default in SQL Server, if left schema is undefined (single-part naming) SQL Server's default behavior is to assume that the user is looking for objects in the default schema -- so, if you simply remove 2-part naming completely this *should* remove this issue entirely.

I have not dug into the code to find where the 'dbo' setting is hard-coded... so, unsure I can provide a patch directly.

Any insight into Issue 2: "Implicit conversion from data type nvarchar to varbinary(max) is not allowed."

...additionally, by default in SQL Server, if left schema is undefined (single-part naming) SQL Server's default behavior is to assume that the user is looking for objects in the default schema

That applies to everything, *except* UDF. Which is why we have to add an explicit schema in there.

Also I hope there is a better way to get this information then calling a stored procedure.

Yes there is:

select u.default_schema_name
from sys.database_principals u
where u.type <> 'R' AND u.name = 'DrupalUserName'

That's even worse :) I'm sure there is something in the metadata of the open connection about the active schema, but not sure if PDO exposes it or not. There should be no need for a query here.

Ahhh.... you want to pull it straight of the PDO properties directly. Unlikely -- I don't see that the driver returns any properties that would get you there... Even if you wanted to kludge of using the PDO::exec (http://msdn.microsoft.com/en-us/library/ff628158.aspx) using the above statement to return it -- you'd be stuck as "PDO::exec does not return results for a SELECT statement."

Doesn't appear that the PDO driver creates any obj that captures any attributes of the login involved in creating the connection (maybe that's a good idea for securities sake).... I looked into the conn.cpp/core_conn.cpp for the PHP SQL Driver - no references to anything that would indicate user-default schema...

...but, that would already be too deep to expect it to be patched in here at the Drupal/SQL level I guess..

So... is the answer: "Use dbo schema and everything will work" fix the "Cannot find either column "dbo" or the user-defined function or aggregate" issue?

Still unsure how to resolve the: "Implicit conversion from data type nvarchar to varbinary(max) is not allowed" problem mentioned in the original post.

Seems that the best way would be SCHEMA_NAME(). We could fetch that immediately after login at the cost of another round trip (which is quite significant in some scenarios, for example on Microsoft Azure).

I think those two errors are likely related. The problem is that we don't properly take into account the default schema, and instead we consider that it is always "dbo".

Here is a patch for review and testing.

I committed the attached patch and tagged a RC3 version. Please test and report back! Tentatively marking as fixed.

Thanks for the quick update/patch Damien ! We'll let you know if anything else crops up.

I had the same issues on 1.2, so I reverted back to 1.1 and it seemed to work...until I logged in as the admin and tried to see content and create new content. Now I'm getting the error again. But the moment I log out, I'm fine.

I can see the list of content and a lot of the admin functions, just nothing else. And I see when I tried to create data, I put in text into all fields and published and I can see that the test content was created as it shows up in the find content listing, I just can't actually see it.

You should visit the install.php page once so that the driver re-creates the missing user-defined functions.

Visiting install.php worked for me too, which is great because I had been running with comment module disabled to avoid the error.