Post-it notes: Chapter 4 – Managing Security

January 22, 2013

Flirting with the idea of taking exam [70-432: Microsoft SQL Server 2008, Implementation and Maintenance] I created loads of post-it notes based on the book: “The Real MCTS SQL Server 2008 Exam 70-432 Prep kit”. I’ve decided to re-visit these notes (before the stickiness wears off and I lose them) and transfer them onto this blog. This will ultimately be a great knowledge refresher and quick reference guide for me. Hopefully you’ll find them useful too. Thanks for reading.

DB ownership chaining


  • Principals = entitity = can request SQL server resource.
  • Every principle is assigned an SID
  • Windows level principal have windows permission scope
    • Domain login, Local login, Group login
  • SQL Server level principal have server-level persmissions
    • SQL login
    • SQL login mapped to WIN Login/Cert or Asym Key
  • Database level principal have db permissions
    • DB User
    • DB User mapped to SQL Login, Win Login, Cert, Asym Key, DB role, App role or Public Role

Hierarchy of Securable Objects

SQL securables


  • Server roles – fixed
  • Database roles – fixed or user (flexible)
  • Logins/users added to these roles
  • Roles have specific permissions and purpose
  • Server roles (sysadmin, serveradmin, securityadmin, processadmin, setupadmin, bulkadmin, diskadmin, dbcreator)
  • Database roles (db_owner, db_security_admin, db_accessadmin, db_backupoperator, db_ddladmin, db_datawriter, db_datareader, db_denydatawriter, db_denydatareader)
  • Number of stored procs and table that you can use to work with roles.


  • Database principals
  • Mapped to login, cert or asym key
  • After create a login, you create a db user


  • Database schemas are collections of objects (tables, views, procedures)
  • Powerful way to manage permissions
  • Users are limited to schemas which they are granted access too
  • Schemas must be transferred to another user/role if schema owner is deleted

Users vs Logins

  • Authentication – let them in
  • Authorization – what can they access
  • Logins provide access to SQL instance. It’s the DB user level (mapped to the login) that provides access to objects contained within databases.

Authentication Mode

  • Mixed Mode Authentication vs Windows Authentication mode.
  • You cannot disable windows authentication
  • Windows authentication, SA account is created but disabled (sys generated password)
  • Change to mixed – enable SA (alter login SA enable, alter login sa with password), create password, restart SQL
  • Win auth recommended – better security.
  • SQL server logins (user must change pswd, expiration and enforce policy)
  • SQL authentication good for (cross domain, 3rd party apps, mixed OS, distributed apps etc)
  • SQL authentication not good for (maintenance, no Kerberos, no additional win policy features)


  • Consider hierarchy of securable, what level you want to apply permission
  • Can apply to DB -> Schema -> Object
  • Permissions (select, view change tracking, update, references, insert, delete, execute, receive, view definition, alter, take ownership and control)

Cross-Database Ownership Chaining

  • Enable at instance level
  • Sp_configure ‘cross db ownership chaining’ ‘1’ or ‘0’
  • ALTER or CREATE stmt on database (SET)
  • Grant permissions on a view, based on two tables, the view permissions are used not the permissions on the individual tables. Slight performance increase.

Object Permissions

  • Can specify an execution context
  • Run code under a specific user, not user who is calling it
  • Only need to grant permissions to the object called.
  • Functions, procedures, triggers – execute as….
    • Caller (before 2008, always caller)
    • Self
    • Owner
    • ‘user_name’
    • ‘login_name’
  • ‘Execute as’ replaces ‘set user’


  • SQL Enterprise edition only
  • Stored in File, Event viewer application, Event viewer security
  • .sqlaudit extension
  • DB Audit (create, alter, drop, insert, update, delete)
  • Process: (1) Create server audit (2) alter server audit – enable it (3) then create the db audit

Change Data Capture (CDC)

  • New SQL 2008 (dev, enterprise and eval only)
  • Capture insert, update and delete
  • Captures column information and metadata
  • Stored in change tables, similar to tracked table
  • Sys.sp_cdc_enable_db_change_data_Capture
  • Sys.sp_cdc_enable_table_change_data_Capture
  • Sys.databases and sys.tables have fields (is_cdc_enabled, is_tracked_by_cdc)

DDL Triggers

  • Keep track of structural changes
  • Fire after the events and are transactional, can be rolled back
  • Prevent column changes or dropped tables
  • Can be set at db or server level
  • Server: Sys.server_triggers  ; database: sys.triggers

SQL Server Configuration Manager

  • SQL 2005 surface area config tool replaced by SQL Server config manager
  • Available as facet in server instance
  • Secure by default
  • Sp_configure

Security and SQL Agent

  • SQL Agent proxies; Proxy based on credential

Service Accounts and Permissions

  • Few changes in 2008, groups are still created at install, but no rights in SQL
  • Only accounts selected during install, have rights granted.
  • Use a domain account for sql service. Should not be local admin.
  • Should not use network account as it’s shareable.
  • Cannot use local service account for SQL or Agent

Thanks for reading.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: