• Logins
• Endpoints
• Databases

• Users
• Roles
• Application roles
• Certificates
• Symmetric keys
• Asymmetric keys
• Assemblies
• Full-text catalogs
• DDL events
• Schemas

• Tables
• Views
• Functions
• Procedures
• Types
• Synonyms
• Aggregates

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.

• 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

• Group Policy defines Windows password requirements
• SQL Server applies password policy to SQL logins
  • Requires Windows Server 2003

CREATE LOGIN login_name
  { WITH SQL_login_optioins
    | FROM WINDOWS [ WITH
    windows_login_options ] }

• Active Directory requirements
  • User trusted for delegation
  • Computer trusted for delegation
• SQL Server requirements
  • Service Principal Name (SPN)
  • TCP/IP

• 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

  CREATE CREDENTIAL credential_name WITH IDENTITY = 'identity_name' [ , SECRET = 'secret' ]

• Server permissions

  USE master
  GRANT ALTER ANY DATABASE
  TO [ADVENTUREWORKS\Holly]

• Server scope securable permissions

  USE master
  GRANT ALTER
  ON LOGIN :: AWWebApp
  TO [ADVENTUREWORKS\Holly]

• 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

• 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

• 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

• 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 permissions

  USE AdventureWorks
  GRANT ALTER ANY USER
  TO HRManager

• Database-scope securable permissions

  USE AdventureWorks
  GRANT SELECt
  ON SCHEMA :: Sales
  TO Salesuser

• User-defined type permissions

  USE AdventureWorks
  GRANT EXECUTE
  ON TYPE :: Person.addressType
  TO SalesUser

• All other schema-scope permissions

  USE AdventureWorks
  GRANT SELECT
  ON Sales.Order
  TO SalesUser

• Symmetric
  • Same key used to enctypt and decrypt
• Asymmetric
  • Pair of values: public key and private key
  • One encrypts, the other decrypts

• Associates a public key with entity that holds that key
• Contents:
  • Identifier information of the subject
  • Validity period
  • Issuer identifier information and digital signature

Service master key - Database master key +- Asymmetric key - Symmetric key
                                         |
                                         +- Certificate

• Data Encryption
• Code module signing
• Impersonation context
• Inter-server authentication