As long as it is only a matter of SELECT, INSERT etc, you can put these statements in a stored procedure and users only need permission to run the procedure. That is, it seems that the stored procedure acts as a container for the permission. But you find that this no longer seems to work when you use dynamic SQL, create non-temp tables or try other "advanced" things. The story is that the procedure can still act as a container for the permission and users do not need to be granted elevated permissions if you take extra steps in form of certificate signing or use EXECUTE AS. In this session you will learn how to use these techniques and why one of them is better than the other.

The session does not stop at the database level, but it also looks at how these techniques can be used to permit users to perform actions that require server-level permission in a controlled way, for instance letting a database owner see all sessions connected to his database but not other databases. As a side-effect of this, you will also learn why the TRUSTWORTHY setting for a database is dangerous.