Course Topics

1. Course Topics

2. 05 | Security Implementation George Squillace | Senior Technical Trainer – New Horizons Great Lakes Richard Currey | Senior Technical Trainer – New Horizons United

3. Module 5 Overview • Server Logins and Server-level Roles • Database Users and Database-level Roles • Implementing Permissions

4. Topic: Server Logins and Server-level Roles

5. Topic: Server Logins and Server-level Roles • Security Scopes • Authentication Modes • Login Objects • Server Roles and Server-level Permissions

6. Security Scopes • Server / instance • Database • Schema • Object • Scope-specific permissions exist • Permission statements • GRANT • REVOKE • DENY

7. Authentication Modes • Two modes • SQL logins are protected better by using password policies • Set during installation and they can be changed later • Switching requires a service restart • Functionally only a registry key change

8. Login Objects • Purpose • Derived from • Windows • Domain • Machine local • SQL logins • Administration tools • SSMS • CREATE / ALTER / DROP LOGIN • Options

9. Server Roles and Server-level Permissions • Types of server-level roles • Fixed server roles • User-defined server roles • Server-scoped permissions • Administration tools • SSMS • sp_AddSrvRoleMember • CREATE / ALTER / DROP SERVER ROLE

10. Implementing Server-level Security

11. Topic: Database Users and Database-level Roles

12. Topic: Database Users and Database-level Roles • Database Users • Database-level Roles • Application Roles • Contained Databases

13. Database Users • Database users • Derived from logins • Types of database-level roles • Fixed • User defined • Application roles • Database-scoped permissions

14. Database-level Roles • Types of database-level roles • Fixed • User defined • Database-scoped permissions • Administration tools • SSMS • CREATE / ALTER / DROP ROLE • sp_AddRoleMember

15. Application Roles • The purpose of an Application Role is toprotect against making connectionsto SQL Server outside of front-end application logic (using Excel or Access, for example) • Instead an application calls an Application Rolethat the end-user is unaware of (and that is assigneddatabase permissions) • Administration tools • SSMS • CREATE / ALTER / DROP ROLE • sp_AddRoleMember • sp_SetAppRole and sp_UnSetAppRole

16. Implementing Database-level Security

17. Contained Databases • A problem with moving and restoring databases is that they have dependencies on login and other objects • A possible solution is a contained database • Requires a server-level and database-level option • sp_Configure N’Contained Database Authentication’ • ALTER DATABASE DBx Set Containment = Partial • Applicable principals • Windows user • Windows group • SQL login

18. Creating Users for a Specific Database (Contained Database)

19. Topic: Implementing Permissions

20. Topic: Implementing Permissions • Object Permissions • Code and Module Permissions • Applying Permissions • Permission Management Strategies “Principals are assigned Permissions to Securables.”

21. Object Permissions • Applies to tables, views and table-valued user-defined functions • Primary permissions • ALTER • CONTROL • SELECT, INSERT, UPDATE, DELETE • VIEW DEFINITION

22. Code and Module Permissions • Applies to scalar user-defined functions and stored procedures • Primary permissions • ALTER • CONTROL • EXECUTE • VIEW DEFINITION

23. Applying Permissions • SSMS • Begin with a principal • Begin with a securable • T-SQL statements • GRANT • REVOKE • DENY • Security model anomaly (prior to SQL Server 2012) • Column-level GRANT override Table-level DENY

24. Permission Management Strategies • Various strategies • Aim for “double abstraction” • Leverage roles • Roles can be nested • Leverage schemas

25. Implementing a Permission Strategy by Assigning a Role Permission to a Schema