Databases SQL SqlServer Software Development database lifecycle management ETL T-SQL SQLCLR Collations Unicode Encodings Module Signing
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: https://SqlQuantumLeap.com/ . Solomon is owner / founder of Sql Quantum Lift ( https://SqlQuantumLift.com/ ), and is the creator of the popular SQLCLR library of functions: SQL# ( https://SQLsharp.com/ ). He does what he can to help others by answering questions on a couple of StackExchange sites ( https://stackexchange.com/users/281451/ ), https://Ask.SqlServerCentral.com/users/20164/SRutzky.html , and https://SqlServerCentral.com/Forums/ .
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 — https://ModuleSigning.info/ — 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.
𝕊tring data is far more complex than you might be aware of. The US English alphabet and punctuation are quite simple, but are only a small portion of the available alphabets and non-language symbols. As the need to handle multiple languages, emoji, etc grows, it's important to understand collations and encodings ｟ https://Collations.Info/ ｠ before getting collation mismatch errors or losing data.
ℂome learn about: the actual differences between ᴠᴀʀᴄʜᴀʀ and ɴᴠᴀʀᴄʜᴀʀ, what Collations are and can do, Collation Precedence, what server-level and database-level default collations do, Code Pages, Unicode, ASCII / UTF-8 / UTF-16 / UCS-2 / multi-byte character set encodings, character-set conversions and "best fit" mappings, SQL Server vs Windows collations, binary collations (and why they aren't truly case sensitive), supplementary characters and surrogate pairs, combining characters, ramifications of changing a database's collation, sorting, normalization, and other fascinating topics 😺.
16 May 2020