Workarounds for setting default schema for domain group?

Question

I am currently working on a project for a client, they have asked me to change the database from Oracle to SQL Server. So we migrated the database across to SQL Server 2008, when this was migrated (Using SSMA SQL Server Migration Assistant) it added a schema name onto the start of all of the table and procedures. Since then we have made lots of changes to some of the procedures within the database.

My problem now is that the application (.NET web app) is calling the procedures without the schema name prefixed. So when it calls the database it cannot find the procedure as it does not have the schema name at the start.

My client has made it clear that the only authentication that they want to use is Windows authentication. From what research I have done I can see that it is not possible to set a default schema for a domain group of users. and it would not be possible for us to add a user in the database for every user that will use the system from that domain group.

Does anyone have any advice on what I could do or a workaround for the default schema issue for domain users.

Thanks

Answer 1

Taking @Mr.Brownstone's idea, you could definitely try this:

DECLARE @sql nvarchar(MAX) = N'';

SELECT @sql += N'
  ALTER SCHEMA dbo TRANSFER custom_schema.' + QUOTENAME(name) + ';'
  FROM sys.objects
  WHERE SCHEMA_NAME([schema_id]) = N'custom_schema';

EXEC sys.sp_executesql @sql;

Now, it may be the case that you have foreign keys etc. that will need to be dropped and re-created (and I've shown how to automate that too), or as I mentioned in a comment, you may have explicit references to custom_schema in your existing objects. You may also have duplicate objects (say, dbo.Customers and custom_schema.Customers) that you'll need to handle. But the general idea should be sound.

Another workaround would be to upgrade to SQL Server 2012, where you can set a DEFAULT_SCHEMA for a domain group. I haven't tested this, but this Connect item says it has been made so.

In either case, I still think the app should be fixed eventually. As I've mentioned many times before, you should ALWAYS use an explicit schema prefix when creating or referencing objects.

Answer 2

If a schema is not specified, dbo is where sql server will look for the object. Are the procedure names unique across the schemas? If so, you could create a synonym for each one in the dbo schema.

Check out the documentation on synonym creation here: http://msdn.microsoft.com/en-us/library/ms177544.aspx

Answer 3

old post, nevertheless I would like to point out an MS article on multi-tenancy where MS itself states not to prefix http://msdn.microsoft.com/en-us/library/aa479086.aspx#mlttntda_topic2