Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.


share|improve this question
Why can't you script out the procs and change the schema in the create statement via a find and replace? This would take minutes not months... – Mr.Brownstone Sep 6 '12 at 16:50

3 Answers 3

up vote 4 down vote accepted

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


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

EXEC 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.

share|improve this answer

old post, nevertheless I would like to point out an MS article on multi-tenancy where MS itself states not to prefix

share|improve this answer
Please add the main points (or an excerpt) from that article - in it's current form your answer is a link-only one which people here tend to not like very much... – dezso Jun 17 '13 at 12:49

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:

share|improve this answer
Actually, the user's default schema is the first place that is checked. Not that referencing an object without the schema is ever a good idea. – Aaron Bertrand Oct 20 at 1:54

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.