Workaround for “Unable to set Default Schema for a group”.

  • Article

Let assume you are using Windows Authentication with SQL Server 2005, you've added a new server account for domain group and would like to give it a default schema. The properties window is the same for users and groups but the default schema field is enabled only for user entities. As a result you cannot add a default schema to a group. So if any member of [domain]\TestGroup will try to create table without explicit schema pointed in a statement (like CREATE TABLE t1 (ID int)), (s)he will always get an error.

 

You can see a long thread about this issue here

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=79418&SiteID=1&PageID=0

I’d like to show you a simple workaround.

Before testing please create a Windows group like [domain]\TestGroup, create a dummy user [domain]\TestUser and make this user be a member of TestGroup.

USE [master]
GO

 

-- create login for the group
CREATE LOGIN [FCOD\TestGroup] FROM WINDOWS WITH DEFAULT_DATABASE=[Northwind]

GO

-- switch to database
USE Northwind

GO

-- create dummy schema

CREATE SCHEMA [dummy] – this is a schema for testing.

GO

-- create a database user for TestGroup (Windows group)
CREATE USER [TestGroup] FOR LOGIN [FCOD\TestGroup]

GO

ALTER AUTHORIZATION ON SCHEMA::[dummy] TO [TestGroup]

GO

-- grant CREATE TABLE privilege, if you need others – please assign theirs here.
GRANT CREATE TABLE TO [TestGroup]

GO

That’s all. Now TestUser can connect to SQL Server, Northwind database, and create table. Please reboot your computer, logon like TestUser and connect to SQL Server. When you will run

CREATE TABLE t1 ( ID int )

you’ll see, that SQL engine will automatically:

- create [domain]\TestUser schema in Northwind

- create [domain]\TestUser database user in Northwind

- create table [domain\TestUser].t1 in Northwind

Any user (member of [domain]\TestGroup) currently can connect to desired server, work with database and even create own tables in own schemas. No other actions required, and no rules required for any new member of TestGroup.

If you like all table be created in dummy schema, you can use explicit schema like

CREATE TABLE dummy.t1 ( ID int )

.