Editor's note: This is part two of a four-part series about SQL Server security. The first part, "Why SQL Server security matters: A case study," was published last week. Check back next week for part three.
Database infrastructure security is extremely crucial for any organization, which is why Microsoft has invested heavily in SQL Server security features in recent years. Microsoft redesigned the SQL Server database security engine from scratch for SQL Server 2005, and since then, SQL Server security has improved with each update.
Microsoft SQL Server 2012 continues this trend with an extensive collection of new security features and enhancements. These enhancements not only help organizations to improve access controls to data, but also to achieve the highest level of data protection and compliance. Also, these features help make SQL Server arguably the most robust common database platform from a security perspective, with less vulnerability and fewer security patches needed to maintain the system.
Security manageability improvements
SQL Server 2012 introduces two small but extremely valuable changes to improve security manageability. The first is the default schema for Windows groups, and the second is user-defined server roles.
Default schema for Windows groups
Before SQL Server 2012 was introduced, it was not possible to specify the default schema for Windows groups. As a result, when the user getting access through Windows group membership created database objects such as a table or view inside a database, SQL Server automatically created a separate user (mapped to the admin account) and a schema with the same name in the database. Because of this security manageability issue, we end up having hundreds of users and schemas inside databases, which caused administrative challenges and is a managerial nightmare for administrators. Hence, SQL Server community requested a fix for this security issue via the Microsoft Connect site.
Luckily, SQL Server 2012 addresses this security issue by allowing us to assign a default schema for Windows Groups, which helps organizations simplify their database schema administration.
The following Transact SQL (T-SQL) demonstrates the process of assigning the default schema for Windows Group:
-- Creating Default Schema "ProdAdmins" for Windows Group "MyDomain\ProdDBAs"
CREATE SCHEMA [ProdAdmins] AUTHORIZATION [MyDomain\ProdDBAs]
-- Set Default Schema for Windows Group "MyDomain\ProdDBAs"
ALTER USER [MyDomain\ProdDBAs] WITH DEFAULT_SCHEMA=[ProdAdmins]
User-defined server roles
The user-defined roles are similar to the fixed server roles; the only difference is that they are created and managed by SQL Server administrators. User-defined roles allow administrators to create and assign server-wide permissions to user-defined roles and then add members to them. The following T-SQL returns the list of these server-wide permissions:
SELECT * FROM sys.fn_builtin_permissions(DEFAULT)
WHERE [class_desc] IN ('ENDPOINT'
ORDER BY [class_desc], [permission_name]
The main advantage of user-defined roles is that they simplify administration by limiting access of authorized users according to segregations of duties.
We can create and manage user-defined roles via T-SQL or via SQL Server Management Studio (SSMS), which is demonstrated below:
User-defined roles using SSMS:
- In the Object Explorer, expand the instance of SQL Server, then expand the Security folder.
- Right-click the Sever Roles folder, then select New Server Role from the menu to launch the New Server Role wizard.
- On the General page, specify the name, owner and appropriate securable for the new user-defined server role.
- Click on Members to activate the Members page, then add members to your user-defined roles.
- Finally, click on the Membership page if you want to add your new user-defined role as a member of the existing fixed server role.
User-defined roles using T-SQL:
We can use CREATE SERVER ROLE, ALTER SERVER ROLE and DROP SERVER ROLE Transact-SQL statements to create, alter and drop user-defined server roles. This is demonstrated as follows:
-- Creating user-defined roles
CREATE SERVER ROLE [JuniorDBA]
-- Granting server-wide permissions
GRANT CREATE ANY DATABASE TO [JuniorDBA]
-- Adding members to user-defined roles
ALTER SERVER ROLE [JuniorDBA]
ADD MEMBER [Domain\JuniorDBA_Group1]
ALTER SERVER ROLE [JuniorDBA]
ADD MEMBER [Domain\JuniorDBA_Group1]
-- Making user-defined role member of fixed server role
ALTER SERVER ROLE [processadmin]
ADD MEMBER [JuniorDBA]
-- Dropping user-defined roles
DROP SERVER ROLE [JuniorDBA]
SQL Server auditing enhancements
Server and database audit specification objects found in SQL Server 2008 and SQL Server 2008 R2 are the most useful features of SQL Server and help the organization meet various regulatory compliance requirements. The problem with these auditing features is that they were only in the enterprise edition.
Fortunately, the server level audit specification features are now supported by all versions of SQL Server 2012. Audit specification features of SQL Server 2012 are more resilient to failures with writing to the audit log, and it is possible to limit the number of audit log files without rolling over. SQL Server 2012 audit specification features also support user-defined groups, which means we can now write audited events to the audit log by using sp_audit_write (Transact-SQL) procedure. Finally, SQL Server 2012 supports the ability to filter the audit events and include new audited groups to monitor contained database users.
Database authentication enhancements
Prior to SQL Server 2012, users required either a Windows or SQL login within the SQL Server database engine to authenticate to the database. Obviously, this dependency caused authentication issues when databases were migrated from one SQL Server instance to another, and database administrators had to ensure that all logins of the source SQL Server also existed on the target SQL Server. If, for some reason, the login did not exist on the target SQL Server, the user would not be able to authenticate the database.
SQL Server 2012 addresses this issue by introducing contained database authentication, which allows users to authenticate their database without having a login on SQL Server. All metadata for contained database authentication is stored inside the contained database itself. The other advantage of contained database authentication is that it eliminates the chances of having orphaned or unused logins on your SQL Server database engine.