SQL2012 It’s The Small Things–Default Schemas For Groups

If you’re reading this then I’m sure you know all about SQL 2012 which just went RTM and will be available for general release on April 1st. I’m sure you’ve also heard all about some of the big new changes such as AlwaysOn with readable secondaries, ColumnStore indexes, and FileTables. Missed in all the big announcements are some of the smaller things that make a huge difference in the day to day running of SQL Server. I’m going to put together a few posts outlining some of the smaller, but to me equally significant, changes that are in the new version.

 

Back in the day there was no facility to provide a default schema to a Windows Group, you would actually get an error were you to try:

USE Master;

CREATE LOGIN [sirsqlDBAs] from windows WITH DEFAULT_DATABASE = TempDB;

 

USE tempdb;

CREATE USER [sirsqlDBAs];

 

ALTER USER [sirsqlDBAs] with DEFAULT_SCHEMA = dbo;

 

image

 

This script now works in SQL 2012 and defines the default schema

image

 

If we check out the properties of the user we can see that the default schema has been set to dbo:

image

 

 

This is a very welcome addition. Prior to SQL 2012 you would have to create a separate login and user for each user in a windows group and then set the default schema for each, that’s a lot of administrative hassle eliminated.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s