A Schema Riddle

Here’s a puzzle I had to untangle this week.

When would a SQL Server sysadmin login not find an existing object in a database, while a plain-vanilla user can?

A colleague came to me the other day with an interesting scenario he could not figure out. He had a tool from an ISV that was supposed to connect to his application’s database and examine it for issues prior to an upgrade, sort of like the SQL Server upgrade advisor, but for a specific third-party application. The issue: every time he ran this tool, it would fail with an error that said that a table didn’t exist in the database, while the table was clearly present and working in all other ways. In Management Studio we could see it, and the actual application, aside from this tool, worked fine.

A clue: the table in question was not in the dbo schema, but in a custom schema called “td.” And the problem tool did not schema-qualify object names: it was all “SELECT column FROM theTable” instead of “SELECT column FROM dbo.theTable” or “SELECT column FROM td.theTable.”

“Aha!” says I to my friend. “Default Schema!” I haughtily bragged about how I could fix this right up.

I set a user up in his database from the Windows Domain account that he was using, and set the default schema to “td.”

“Whew, that was easy.” I thought. “I thought he had some real stumper.”

Wrong.

“Still broken,” reports my friend. “Same error.”

“OK,” I say. Scratching my head a little now. “Let me go back to my desk and try to repro this. If we specify a default schema for a user, SQL Server ought to look in that schema first for an unqualified object name, and only switch to the dbo schema if it fails to find a match.”

So, my login is a sysadmin on the server in question. Back at my desk, I connect and try:

SELECT column FROM theTable

<fail>

Msg 208, Level 16, State 1, Line 1 Invalid object name 'theTable'.

“OK, that makes sense.” Talking to myself – I do that sometimes. “I am a system administrator, and therefore have no default schema in this database, so the server’s going to check dbo and then fail.”

SELECT column FROM td.theTable

<success>

“Whew. Now if I impersonate my buddy, that user has the right default schema, and should work.”

EXECUTE AS USER = ‘myDomain\theOtherDude’

SELECT column FROM theTable

<success>

OK, so that user works like I’d expect – it’s looking in its default schema first, and finding the object. (The astute reader will see my mistake at this point in the story.) I call the guy, 'cause this should work.

“You are sure that’s not working?” I am grateful by now that he is polite and flexible.

“Yes,” says my friend. “Positive.”

“Hmm.”

I quickly make a temporary SQL Server account just for this purpose, set it up in the database with the correct default schema, test it (it works) and tell him to use those credentials for his one-time utility. He can then connect and see the table.

Riddle

Why wouldn’t his own windows account work? Why did I have to make another SQL Server account for him?

Answer

BUILTIN\Administrators. While we disable BUILTIN\Administrators on most servers, his case was an exception. His Windows account was a local admin on the machine, which flowed through to the SQL Server, making him a sysadmin. And it turns out that even if you set the default schema for a database user, if that user is linked to a sysadmin login, it doesn’t do the expected unqualified name resolution in the database, but always acts like a sysadmin.

My repro was invalid, because I used EXECUTE AS USER. That gave me the impersonation of the database-level rights for his account, but not the server-level role, which of course was in effect when he actually ran his utility. Had I impersonated the login and not the user, then I think I would have seen the failure right in Management Studio.

Joke’s on Me

Ultimately, the joke’s on me, though. The second check that my friend’s tool makes, directly after the problem SELECT statement? Verify that the account running the tool is a SQL Server sysadmin. Catch-22. We will have to make a copy of his database, move all the objects to the dbo schema, then run the utility on the copy, because we can’t set both a default schema and the sysadmin role at the same time.

After that, we’ll have to “fix” his databases which, it seems, were only set up with this funny “td” schema because the person who performed the installation didn’t have the login settings right at setup time.

Don’t get me wrong, I love schemas when implemented correctly. But always use two-part names, folks. No joke!