Loading Events

« All Events

  • This event has passed.

Module Signing or: How I Learned to Stop Using EXECUTE AS and Love Certificates

November 21, 2017 @ 6:30 pm

We often need to grant a higher-than-ideal level of permissions to a Login or Role that is necessary to perform a task. Someone needs to start a SQL Agent job without being able to edit any jobs. Or, someone needs data from a DMV that requires the “VIEW SERVER STATE” permission, but they shouldn’t see everything that is accessible via that permission. The common solution to address what ownership-chaining doesn’t is to use some combination of: EXECUTE AS (i.e. Impersonation) and/or cross-DB ownership chaining and/or TRUSTWORTHY. Yet all of those options 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 and asymmetric keys work, and rejoice.

Solomon Rutzky has been working with computers since the mid 1980s. He has experience with a variety of hardware platforms, OSs, programming languages, and RDBMSs. He has focused on SQL Server since 2002, and has written articles for SQL Server Central, including the Stairway to SQLCLR series, and SimpleTalk. Solomon is the founder / owner of Sql Quantum Lift, and is the creator of the popular SQL# SQLCLR library. He answers questions on a few StackExchange sites and Ask.SqlServerCentral.com, and occasionally blogs: Sql Quantum Leap.