Name clash with sys.sysusers system view in SQL Server

Question

(Note that this was on SQL Server 2008, but I have a colleague who reports the same issue on SQL Server 2014.)

I'm using a framework that supports multiple database back-ends, and our application has a table called sysUsers, which works fine in MySQL.

We now need to install it on SQLServer and it appears that this name conflicts with a built-in system view. The system view is sys.sysusers and the application table is dbo.sysUsers.

I am aware that the case difference is irrelevant to SQL Server, however the schema seems to be being ignored for some reason.

• SELECT * FROM sys.sysusers; returns records from sys.sysusers. This is wholly as expected.
• SELECT * FROM sysUsers; returns records from sys.sysusers. This is surprising (I would have thought the local schema would take precedence) but perhaps explicable.
• However, SELECT * FROM dbo.sysUsers; still returns records from sys.sysusers. This seems just plain wrong as I am explicitly selecting the dbo schema.

I haven't found anything in the MS documentation that says these names are reserved.

I have tried renaming the table and hacking the code to use a different name, and everything works (i.e. this is nothing to do with the SQLServer integration within the application) and the same results are seen when running the queries from the management console directly. Therefore this appears to definitely be an issue with the conflicting table name and not a middle-ware error or syntax difference.

If this table name is reserved, why does MSSMS allow me to create it? If it is not reserved, why does it not let me query it?

And how can I work round the problem without requiring application updates (as these would be a migration headache for other deployments).

Answer 1

There are at least three workarounds, but none guarantee that no code has to be rewritten (except the one that's horribly unsafe):

• Use a case-sensitive collation when creating your database (CREATE DATABASE Foo COLLATE Latin1_General_CS_AS). In this case, sysUsers will be a different object from sysusers, in all circumstances. You can set a case-insensitive collation immediately after creating the database so your data doesn't end up case-sensitive, as this is probably not what the end users want. Obviously this won't work if your application is actually relying on case-insensitive object names, unless you rewrite your queries carefully. Note that this means that all database objects, even those created afterwards, will have case-sensitive names, as this is embedded in the system tables on creation and can't be changed afterwards.
• Use a schema other than dbo. The system table mapping occurs only for that scheme, not any others. If your application uses its own schema exclusively, any sysusers you create in that will not be aliased to sys.sysusers. (This isn't documented anywhere, but it is how it works.) Note that in order for this to work, you must always specify the schema explicitly even when it is the default schema for your user, otherwise you will again get the system table (I'd consider this a bug, but it's probably a necessity because of the way old scripts will assume sysusers resolves anywhere).
• Enable the Dedicated Administrator Connection, restart SQL Server in single user mode, switch the mssqlsystemresource database to READ_WRITE and DROP VIEW sysusers. This will remove sys.sysusers from all databases. Doing this will void your warranty, it will cause Microsoft Support to laugh at you if you come crying to them, it may make installing future Service Packs and updates impossible and is emphatically not recommended, but I'm mentioning it anyway, for science. No code anywhere should be using this view, but, you know, I'm not an engineer working on SQL Server itself.

Note that lowering the compatibility level is not a workaround, which I mention for completeness. This has no effect on how these table names are resolved, even if it was a desirable approach (which it's not).

I consider the change made in SQL Server 2012 to ignore the dbo qualifier and resolve to these old, deprecated names anyway a mistake and if it were up to me I'd at least make it possible to opt out of this behavior with a trace flag, but it's not up to me. You could consider opening up an issue on Microsoft Connect for it, because the current behavior makes it needlessly complicated for RDBMS-agnostic code to run.