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

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?

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.

SQL has long resolved similar issues for logins that are Windows groups. For instance, if a Windows user is a member of two Windows groups that are in turn created as logins on a SQL Server machine, SQL is able to resolve possible differences in default database, language, etc. So it is beyond any possible justification that Microsoft has yet to in some way resolve this for default schema. Defaulting to the dbo schema in such a situation would certainly be an acceptable solution so long as the user is not automatically granted alter on the dbo schema.

At present, if a user that does not have a default schema by virtue of accessing via a group creates an object without qualifying it (ie: “create table” rather than “create dbo.table”) will automatically cause a new schema to be created named after themselves. This user is then granted full ownership of this new schema. This happens even when the group login/user that they used to get to the database is EXPLICITLY DENIED create schema rights. This is totally unacceptable. A user should not be able to create a schema when they are denied that permission. Microsoft has tried to claim that the user is not actually creating the schema, that SQL Server actually is. While this may technically be true, it in fact always it technically true. Any user that creates a schema does so by submitting a request to SQL Server to create it, and SQL Server then creates is. In either case, the user caused SQL Server to create it so the attempt at justifying this by saying that the user didn’t violate the “deny create schema” fails the test of basic logic

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.

In SQL 2012 we can have default schema for windows groups as well.