I'm attempting to create a stored procedure that's able to create and drop tables. A given user will have EXECUTE permissions on that stored procedure. Is there any way that the user would be able to successfully execute the stored procedure without having the database-wide permissions granted as well for creating and dropping tables? Some initial research has shown that certificates might be a way to solve this but I'm not sure if even those are suitable for creating and dropping without database-wide permissions being granted. In Oracle this is solvable through granting the owning schema rights, but I don't think this concept is mirrored in SQL Server.
Another possible solution that comes to mind would be using EXECUTE AS in the stored procedure to some user that has access to what we need it to, maybe even dbo. This would then let the user execute just that stored procedure but the procedure would have all the permissions it needs to operate. We'd ideally not use this though as this is a vendor stored procedure so we don't want to touch it if we don't have to. Any other ideas would be welcome.
So we have common language let's say I'm using abc for user and test_proc for procedure.
Best Answer
Create a stored procedure that uses EXECUTE AS OWNER to run the procedure in question.