Why Can't I Set a Default SQL Server Schema for My Windows Group Login?

Problem

My application doesn't use the dbo schema and I need to set the users coming into the database to use the correct schema automatically. We're using Windows groups and whenever I go to set a default schema, SQL Server gives me an error. How can I set the default schema for my users?

Solution

The short answer is that if you're using Windows groups, you can't. While the best practice is to use Windows groups because that makes security management much easier on the DBA, one area that's not true is when you need to specify a default schema.

As of SQL Server 2005, users in a database can be set to have a default schema except for the following cases:

• Windows groups
• Logins mapped to certificates 
• Logins mapped to asymmetric keys

We'll focus on Windows groups. If you try to specify a default schema, as in the following example:

You get this error:

This is not a bug; it is the expected behavior. While you can set other default settings, like default database and default language, these correspond to the server level of SQL Server and not an individual database. So why is there a difference? It has to do with the fact that a given Windows user is typically a member of multiple Windows groups. More than one of those Windows groups could have access to the SQL Server and to a specific database, especially in larger environments. For instance, as a DBA you might be a member of the following groups at the domain level (I've using generic names as each organization has different naming conventions):

• Domain User
• Full Time Employee
• Information Technology Member
• Database Administrator

And for a given SQL Server, the groups Database Administrator and Full Time Employee might both access. In the event that both have access, when we're talking about default database and default language, it's not a big deal if we have multiple values as long as they are all valid. After all, both can be overridden when connecting to the SQL Server, even if there is an issue. They also can be overridden after the connection is made. However, when it comes to default schema, we have a problem. For instance, imagine if we could do the following:

• Database Administrator -> Admin
• Full Time Employee -> Employee

When a DBA connects and tries to execute a stored procedure in the database, does SQL Server execute Admin.SomeStoredProcedure or Employee.SomeStoredProcedure? What if there are multiple groups? How do you know for a particular user which schema should be default? The only way to solve the confusion is to specify per user. And if you're going to do that, there's no point specifying the default schema for a Windows group. One could argue that if a particular user is a member of only one group, then a default schema should be specified. However, because of the fact that the association with groups occurs outside of SQL Server, this can lead to unexpected behavior. Also, if a second group is added to the database which the user is a member of, we no longer have the single group situation. Then how should SQL Server react? There's no simple way to solve this issue. That's why default schemas aren't allowed for Windows groups. 

The Workaround

Unfortunately, the only real workaround if users access SQL Server directly is to add users individually to SQL Server and to the database. This doesn't work if you have many users, but because specifying default schema gets to a per user setting, it's really the only way to go. There are ways, such as through PowerShell to script the members of a Windows group and then generate the T-SQL to create the logins, then add them to the database and set the default schema, but you'll be running this regularly to keep everything up to date. However, if you have an application that has a schema other than dbo and users cannot specify the schema, you're not left with any other choice.

If users are accessing through an application, such as a web service, then using a Windows user account for the web service is an option, which allows you to specify the default schema. This assumes, of course, that the web service or application is handling security outside of SQL Server. If it's not, then this isn't a viable option either, and you're forced back to the workaround. In this situation, resorting to a SQL Server-based login doesn't work either, because there is no way SQL Server can tell between Mary and John accessing the DB through the application. You'll have to drop back to specifying the users to be passed through and create them in the database individually.

Source collected from MSSQLTIPS.COM