====== Lesson 1: Overview of SQL Server Security ======
===== What Are Principals? =====
^  Level  ^  Principals  ^
| Windows | Windows local user account\\ Windows domain user account\\ Windows group |
| SQL | SQL Server login\\ SQL Server role |
| Database | Database user \\ Database role \\ Application role |

===== What Are Securables? =====
==== The Server Scope ====
  * Logins
  * Endpoints
  * Databases

==== The Database Scope ====
  * Users
  * Roles
  * Application roles
  * Certificates
  * Symmetric keys
  * Asymmetric keys
  * Assemblies
  * Full-text catalogs
  * DDL events
  * Schemas

==== The Schema Scope ====
  * Tables
  * Views
  * Functions
  * Procedures
  * Types
  * Synonyms
  * Aggregates

===== SQL Server 2005 Permissions =====
Permissions are the rules that govern the level of access that principals havt to securables. You can grant, revoke, or deny access over SQL Server securables to SQL Server principal.

====== Lesson 2: Protecting the Server Scope ======

===== What Are SQL Server Authentication Modes? =====
  * Windows Authentication mode
    * Users are authenticated by Windows
    * Users access SQL Server through a login mapped to their Windows account
  * SQL Server and Windows Authentication mode
    * Users connecting over a trusted connection access SQL Server by using Windows authentication
    * Users connecting over a nontrusted connection are validated by SQL Server

===== How Password Policies Work =====
  * Group Policy defines Windows password requirements
  * SQL Server applies password policy to SQL logins
    * Requires Windows Server 2003

===== How to Manage SQL Server Logins =====
<code>
CREATE LOGIN login_name
  { WITH SQL_login_optioins
    | FROM WINDOWS [ WITH
    windows_login_options ] }
</code>

===== What Are Fixed Server Roles? =====
^   Role        ^  Description                             ^
^ sysadmin      | Perform any activity                     |
^ dbcreator     | Create and alter databases               |
^ diskadmin     | Manage disk files                        |
^ serveradmin   | Configure server-wide settings           |
^ securityadmin | Manage and audit server logins           |
^ processadmin  | Manage SQL Server processes              |
^ bulkadmin     | Run the BULK INSERT statement            |
^ setupadmin    | Configure replication and linked servers |

===== Requirements for Delegation =====
  * Active Directory requirements
    * User trusted for delegation
    * Computer trusted for delegation
  * SQL Server requirements
    * Service Principal Name (SPN)
    * TCP/IP

===== Whar Are Credentials? =====
  * Alternative representations of logins
  * Enable users connecting to SQL to connect to resources outside SQL Server
  * Usually consist of Windows user name and password
  * Creating a Credential <code>
CREATE CREDENTIAL credential_name WITH IDENTITY = 'identity_name' [ , SECRET = 'secret' ]
</code>

===== Server-Scope Permissions =====
  * Server permissions <code>
USE master
GRANT ALTER ANY DATABASE
TO [ADVENTUREWORKS\Holly]
</code>
  * Server scope securable permissions <code>
USE master
GRANT ALTER
ON LOGIN :: AWWebApp
TO [ADVENTUREWORKS\Holly]
</code>

====== Lesson 3: Protecting the Database Scope ======
==== How to Manage Users ====
  * Provide access to individual databases
  * Can map to an individual user login or a Windows group login
  * Created using SQL Server Management Studio or CREATE USER statement

==== Special Users ====
  * dbo user
    * Exists in all databases by default
    * Members of **sysadmin** role and the **sa** login
    * Any object a member of **sysadmin** creates automatically belongs to **dbo**
    * Cannot be deleted
  * guest user
    * Exists in all databases by default
    * Disabled by default
    * Allows logins with no user account to access the database

==== What Are Database Roles? ====
  * Fixed database roles
    * Granted permissions for common database tasks
  * User-defined database roles
    * Group users with similar permission requirements
  * Public role
    * Contains all users in the database

==== What Are Application Roles? ====
  * Application is associated with an application role
  * Alternative security context for a user only exists while the application is active
  * Create using SQL Server Management Studio or the CREATE APPLICATION ROLE statement

==== Database-Scope Permissions ====
  * Database permissions <code>
USE AdventureWorks
GRANT ALTER ANY USER
TO HRManager
</code>
  * Database-scope securable permissions <code>
USE AdventureWorks
GRANT SELECt
ON SCHEMA :: Sales
TO Salesuser
</code>

==== Schema-Scope Permissions ====
  * User-defined type permissions <code>
USE AdventureWorks
GRANT EXECUTE
ON TYPE :: Person.addressType
TO SalesUser
</code>
  * All other schema-scope permissions <code>
USE AdventureWorks
GRANT SELECT
ON Sales.Order
TO SalesUser
</code>

====== Lesson 4: Managing Keys and Certificates ======
===== What Are Keys? =====
  * Symmetric
    * Same key used to enctypt and decrypt
  * Asymmetric
    * Pair of values: public key and private key
    * One encrypts, the other decrypts

===== What Are Certificates? =====
  * Associates a public key with entity that holds that key
  * Contents:
    * Identifier information of the subject
    * Validity period
    * Issuer identifier information and digital signature

===== SQL Server Cryptography Architecture =====
<code>
Service master key - Database master key +- Asymmetric key - Symmetric key
                                         |
                                         +- Certificate
</code>

===== When to Use Keys and Certificates =====
  * Data Encryption
  * Code module signing
  * Impersonation context
  * Inter-server authentication