Solomon Rutzky

Information & Communications Technology

Databases SQL SqlServer Software Development database lifecycle management ETL T-SQL SQLCLR Collations Unicode Encodings Module Signing

Module Signing: Use Certificates to Grant Higher-level Permissions Without Compromising Security

We often need to grant a higher-than-ideal level of permissions to a Login or Role so that someone can: start a SQL Agent job (but you don't want them to edit anything), get data from a DMV that requires "VIEW SERVER STATE" permission (but that permission gives access to too much data), query across databases, TRUNCATE a table, etc. These problems are usually solved with some combination of: EXECUTE AS (i.e. Impersonation), cross-DB ownership chaining, or TRUSTWORTHY ON. Unfortunately, those options are all security risks.

Module Signing — — is more flexible and secure, but requires a Certificate or Asymmetric Key. Those can be confusing to work with, and the security mechanism isn't intuitive. However, that confusion ends here.

Come learn how to have more secure, granular permissions that handle cross-DB tasks, Dynamic SQL, and SQLCLR. See what module signing can do, how certificates and asymmetric keys work, and realize it's not as hard as you thought.

Solomon Rutzky

Head Honcho, Sql Quantum Lift

Solomon Rutzky has been working with databases for since 1996, focusing on SQL Server since 2002. He has written several articles for SQL Server Central (including the Stairway to SQLCLR series), and SimpleTalk, and has a blog: . Solomon is owner / founder of Sql Quantum Lift ( ), and is the creator of the popular SQLCLR library of functions: SQL# ( ). He does what he can to help others by answering questions on a couple of StackExchange sites ( ), , and .

Solomon's full speaker profile