SQL Server 2012 (“Denali”): New Security Features

SQL Server 2012 has many new security features, and three of the bigger new features are: Default Schema for Windows Groups, Audit enhancements, and User-Defined Server Roles.

Default Schema for Windows Groups

A database schema can now be tied to a Windows Group rather than an individual user in order to increase database compliance.  This makes it easier to administer database schemas, decreases the complexity of database schema management through individual Windows users, prevents errors of assigning a schema to the wrong user when a user changes groups, avoids unnecessary implicit schema creation, and by reducing the chances of choosing the wrong schema then it greatly reduces the chance of query errors.

Audit Enhancements

Enhancements to SQL Server auditing capabilities bring added flexibility and usability for auditing across the SQL Server environment, helping to make it even easier for organizations to meet compliance policies:

  • SQL Audit is now available for all editions of SQL Server (previously was only available via the Enterprise edition
  • User-Defined Audit allows an application to write custom events into the audit log to allow more flexibility to store audit information
  • Audit Filtering provides greater flexibility to filter unwanted events into an audit log
  • Audit Resilience delivers the ability to recover auditing data from temporary file and network issues

User-Defined Server Roles

User-Defined Server Roles increase flexibility, manageability, and facilitates compliance towards better separation of duties.  It allows creation of new server roles to suit different organizations that separate multiple administrators according to roles.  Roles can also be nested to allow more flexibility in mapping to hierarchical structures in organizations.  It also helps prevent organizations from using sysadmin for database administration.

More Info:

SQL Server Code Named Denali User Defined Server Roles Demo

SQL Server Code Named Denali User Defined Audit and Audit Filtering Demo

What’s New topic for Security Enhancements on MSDN

TechEd Video: What’s New in Security for Microsoft SQL Server
Code-Named “Denali”

SQL Audit 101 – Creating a basic audit

About James Serra

James currently works for Microsoft specializing in big data and data warehousing using the Analytics Platform System (APS), a Massively Parallel Processing (MPP) architecture. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence/MDM architect and developer, specializing in the Microsoft BI stack. He is a SQL Server MVP with over 25 years of IT experience.
This entry was posted in Security, SQL Server, SQL Server 2012, SQLServerPedia Syndication. Bookmark the permalink.

One Response to SQL Server 2012 (“Denali”): New Security Features

  1. Samir Kazi says:

    Hi James,
    Your blog really hlp me to understand the new features of sql server 2012..
    Thnks alott and very nicely written

    Samir Kazi

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>