Exam 70-458 – Objective 2 – Implement Security

Manage logins and server roles

• Configure server security (http://msdn.microsoft.com/en-us/library/bb283235.aspx)
• Secure the SQL Server using Windows Account/SQL Server accounts, server roles
  • Server-Level Roles (http://msdn.microsoft.com/en-us/library/ms188659.aspx)
• Create log in accounts (http://msdn.microsoft.com/en-us/library/aa337562.aspx)
• Manage access to the server, SQL Server instance, and databases
  • Database Roles (http://msdn.microsoft.com/en-us/library/ms189121.aspx)
• Create and maintain user-defined server roles (http://msdn.microsoft.com/en-us/library/ee677627.aspx)
• Manage certificate logins
  • CREATE LOGIN Example D (http://msdn.microsoft.com/en-us/library/ms189751(v=sql.120).aspx)

Manage database permissions

• Configure database security
• Database level permissions
  • Database Roles (http://msdn.microsoft.com/en-us/library/ms189121.aspx)
• Protect objects from being modified
  • DENY (http://msdn.microsoft.com/en-us/library/fooc32d1e01-9ee9-4665-a516-fcfece58078e.aspx)
  • REVOKE (http://msdn.microsoft.com/en-us/library/foo9d31d3e7-0883-45cd-bf0e-f0361bbb0956.aspx)

Manage users and database roles

• Create access to server/database with least privilege
• Manage security roles for users and administrators
• Create database user accounts
• Contained logins
  • http://blogs.msdn.com/b/sqlsecurity/archive/2010/12/03/contained-database-authentication-introduction.aspx

Troubleshoot security

• Manage certificates and keys (http://msdn.microsoft.com/en-us/library/bb895327.aspx)
• Endpoints (http://msdn.microsoft.com/en-us/library/ms181591(v=sql.120).aspx)

Preparation resources

• Server-level roles
• Permissions (database engine)
• Database-level roles
• http://msdn.microsoft.com/en-us/library/bb510589.aspx