Securing SQL Server 2012
- A securable is an item for which you can assign permissions (what you can secure) these securables can be contained within other securables
- A scope is a container. Permissions assigned at the scope level are inherited by objects within that scope.
- The GRANT, DENY, and REVOKE statements are used to manage permissions on objects.
- Roles enable you to simplify the management of permissions. Assign permissions to roles and then add principals to roles.
- Fixed database roles have fixed permissions. Flexible database roles enable you to assign custom permissions.
- Schemas enable you to collect objects to simplify the process of assigning permissions
- Group Policy items determine account lockout settings.
- The ALTER LOGIN Transact-SQL statement can unlock locked SQL Server–authenticated logins and force password changes.
- Windows authentication mode disables SQL Server authentication mode.
- Mixed authentication mode uses both Windows authentication and SQL Server authentication.
- Verify the expiry dates of certificates when troubleshooting certificate-based security. Use the sys.certificates catalog view to view certificate properties.
- Use the sys.endpoints catalog view to view endpoint information.
- Use the sys.server_principals catalog view to determine whether a principal is disabled
- SQL Audit enables you to track specific actions on the instance or database level.
- SQL Audit can write audit data to the Windows Security or Windows Application log. Audit data can also be written to a normal file.
- An audit can be configured so that the instance shuts down in the event of an audit failure.
- Action groups and actions determine which activity is audited.
- You can create a server or database audit specification only after a server audit has been configured.