Take the 2-minute tour ×
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.

Thanks

share|improve this question
1  
Sorry to be harsh, but either fix the app, or stop using domain groups. –  Aaron Bertrand Sep 6 '12 at 14:58
    
I wish I could. I didnt create the app and if i had to go through all of the procedure calls it would take weeks/months there are thousands. Also the client wants domain groups. There must be another way? –  Glen Robson Sep 6 '12 at 14:59
    
The answer by Raul Garcia might be helpful, not sure: social.msdn.microsoft.com/forums/en-US/sqlsecurity/thread/… –  Aaron Bertrand Sep 6 '12 at 15:01
2  
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
1  
@AaronBertrand I wanted to know if it was possible to move the procs to dbo as the app assumes the procs are there. –  Mr.Brownstone Sep 6 '12 at 17:25

3 Answers 3

up vote 3 down vote accepted

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 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
    
Yes it is looking like your first idea (@Mr.Brownstone's idea) is the one I am going to have to use. I agree that the app should be fixed. The reason it is this way is because previously it was for an oracle database and therefore did not have any schema names. We also do not have the funding from the client to change the application. –  Glen Robson Sep 7 '12 at 8:21

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

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 http://msdn.microsoft.com/en-us/library/aa479086.aspx#mlttntda_topic2

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

Your Answer

 
discard

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.