Topic:
* Please note that this presentation is approximately 75 minutes long *
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 roles.
Speaker:
Erland Sommarskog, Consultant Erland Sommarskog SQL Konsult AB
Erland Sommarskog is an independent consultant based in Stockholm, working with SQL Server since 1991. He was first awarded SQL Server MVP in 2001, and has been re-awarded every year since. His focus is on systems development with the SQL Server Database Engine and his passion is to help people to write better SQL Server applications.