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.
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.