Module Signing or: How I Learned to Stop Using EXECUTE AS and Love Certificates
Duration: 60 minutes
Track: Application & Database Development
Often enough we need to grant higher-level than ideal permissions to a Login or Role that are necessary to perform a task. For example, someone needs to start a SQL Agent job without being able to edit any jobs. Or, a task needs data from one of the DMVs that requires the "VIEW SERVER STATE" permission, but you don't want the user to see everything that is made available by that permission. When ownership-chaining doesn't work, the common solution is to use EXECUTE AS (i.e. Impersonation). Sometimes this works, but cross-DB tasks often require enabling cross-DB ownership chaining and/or TRUSTWORTHY, which are security risks.
Module Signing is more flexible and more secure, but requires a Certificate or Asymmetric Key. Those can be confusing to work with, and the security mechanism isn't intuitive.
Come learn how to have more secure, granular control over permissions that covers cross-DB tasks and Dynamic SQL. See what signing can do, understand how certificates work, and rejoice.