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;

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

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

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.