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”
