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


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 )


Comments (1)
  1. Jeffrey Schenk says:

    Works Great–But you lose visibility into specifically which user is making edits, changes, etc., whereas with straight forward group membership you don’t.

Comments are closed.

Skip to main content