11th Sep 24 | East Midlands Data | Nottingham | Speaker - Erland Sommarskog

___________________________________________________________________________________

MeetUp Agenda

18:00 - Arrive. Networking
18:30 - Guest Speaker Talk
19:45 - Pizza. Networking
20:30 - Event Close

___________________________________________________________________________________

Packaging Permissions in Stored Procedures

The basic mechanism when we want to give users access to data or actions in SQL Server in a controlled way through stored procedure is ownership chaining. This is something we use every day as SQL workers, although we may not be aware of it. Every once in a while, we run into situations where it seems that it is not sufficient to put a statement in a stored procedure, but that users need to be granted explicit permissions – something we may not always be comfortable with, because that would permit the users to do things we do not want them to be able to.

There are two additional mechanisms in SQL Server we can employ in these situations: certificate signing and the EXECUTE AS clause. In this session I will start by exploring how ownership chaining works and its limitations. I will then proceed to explore the other two options and I will particularly focus on certificate signing, as this is the preferred method. At first it may seem overly complex, but I will show how it easily can be automated. EXECUTE AS may seem simpler, but I will point out potential problems with it, not the least if you try to use it on server level and consider to set the database as TRUSTWORTHY.

This session is directed towards both developers and DBAs, as this session deals with problems you may face in either role. The level is 400, mainly of the reason that you will encounter many concepts.

___________________________________________________________________________________

Venue Website

https://www.castlerockbrewery.co.uk/pubs/vat-and-fiddle

___________________________________________________________________________________

MeetUp Member Resources In GitHub Here:

https://github.com/EastMidlandsData

Including introduction slides and our Code of Conduct.

___________________________________________________________________________________