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
Best Answer
Taking @Mr.Brownstone's idea, you could definitely try this:
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
andcustom_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.