SQL, Analysis Services & related stories.

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 )

Published Saturday, January 27, 2007 11:02 PM by Igor Kovalenko

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Jeffrey Schenk said:

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

July 4, 2007 3:40 PM

Leave a Comment

(required) 
(optional)
(required) 

  
Enter Code Here: Required
Submit

About Igor Kovalenko

I've been in IT since 1991 starting my carrier on Unix & C development. Now i am a consultant in Microsoft Services, Russia. My areas of experience - SQL & OLAP. I've been working with Microsoft tools for more than 15 years, started from asm 5.0 and Quick C 2.51 through (Visual) FoxPro, VB, C#... But my mission is SQL. Truly says i have enough knowledge both Oracle (8, 9.i) and Microsoft db technology, but it's to hard for me to cover both :-). My real data warehousing experience started with one of the largest DW implementation with using Oracle 9i in Russia till 2002. Of course i also implemented the first part of BI project on top of this DW with using SQL AS 2000 & Crystal reports. After that for a year i was a seniour developer, Online Services, in Dell UK, Bracknell (c++/vb/Oracle/SQL/ASP). In 2003 i was a little bit tired from High Technology World and decided to join Deloitte, Moscow, where i was a Finance analyst, member of Business Director Group. I really miss a half of my IT knowledge this time (SQL & Crystal is only useful), but now i perfectly know the "underground" of any BIG 4 consulting company, budgeting and managing process details, FTE, Utilization, OPTS analysis.... Hell, real accounting hell. I was excited to design and implement my first (and last) project with using Cognos EP tool. At the end of 2005 i was hired by my favorite company :-) Microsoft and now i am working with my favorite tool: SQL Server. To keep a long story short :-).

© 2009 Microsoft Corporation. All rights reserved. Terms of Use  |  Trademarks  |  Privacy Statement
Microsoft
Page view tracker