Erland Sommarskog
sommarskog.se
Erland Sommarskog is an independent consultant from Stockholm. He has been working with SQL Server since 1991. He was first awarded SQL Server MVP in 2001, and has held the award since.
SQLBits 2019
Packaging Permissions in Stored Procedures
Learn in this session how you can package any database- or server-level permission in a stored procedure with help of certificate signing or EXECUTE AS and why one method is to prefer over the other.
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.
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.