Wednesday, October 23, 2019

Idera GeekSync: SQL Security Principals and Permissions 101

Thanks to Idera and all 239 of you who joined the Idera GeekSync webinar today for my topic on SQL Security Principals and Permissions 101.

Fun fact: Principals and Principles have totally different meanings, but both apply. Principals - in that each login, role, group, user, etc., in SQL Server is a principal - as in an important person, or, in one of the definitions, "a person for whom another acts as an agent or representative." But also, the presentation is about Principles - as in good guidance, or "a fundamental truth or proposition that serves as the foundation for a system of belief or behavior or for a chain of reasoning."

We discussed both! Not only a 101-level intro to authentication, logins and users, but also guidance on migrating and dealing with logins and users, SQL Injection, vulnerability and threat detection, data provider updates, permissions for non-sysadmins, separate accounts for sysadmins, and the nature of permissions in/around SQL Server stored procs, views, and functions.

Got good feedback from Idera on the comments, hope it was a useful and entertaining (albeit fast-paced because of time) review of SQL Server Security and Permissions for you.

PowerPoint available for download here:

Lab scripts for the sproc/view/udf permissions demo:

[UPDATE] Idera has posted the recording here:


Unknown said...

Awesome training! Is there a source for the voiceover?

w said...
This comment has been removed by the author.
Solomon Rutzky said...

Hi there. Nice presentation. I tried to leave a comment over at SlideShare, but am having problems logging in there.

Wanted to mention a few notes:

* Slide 36 (35 on SlideShare): minor point, but "ALTER ANY EVENT SESSION" and "VIEW SERVER STATE" are login permissions, not user permissions.

* Slide 41 (40 on SlideShare): for displaying current login, use SYSTEM_USER since ORIGINAL_LOGIN() won't show the impersonated login name.

* Slide 45 (44 on SlideShare): Absolutely agree with avoiding EXECUTE AS. Please see https://ModuleSigning.Info/ for additional information on avoiding it and the more evil "TRUSTWORTHY ON".

* Slide 47 (46 on SlideShare): The "Feature Restrictions" feature was disabled for the RTM release, and the documentation has been fully removed. For full details, please see:

Take care,

w said...

Thanks Solomon!