Granting Permissions through Stored Procedures
You probably know that with a stored procedure you can permit users to access or update data even if they have no direct permissions on the table. However, this does not work with dynamic SQL.
Likewise, say that you for instance want power users be table to see who are connected to their database without seeing any other sessions. What if you could package this into a stored procedure without having to grant any server-level permissions?
In this session we will learn to exactly this. In fact we will learn two techniques: certificate signing and EXECUTE AS. We will learn that one is preferrable over the other, and as a by-product of this discussion, we will learn we should be cautions of making a database TRUSTWORTHY.
The session should be of interest both to DBAs and application developers that need to consider advanced permissions in their stored procedures.