SQL Server CREATE and DROP TABLE Permissions in Stored Procedure Only

Question

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.

Answer 1

Create a stored procedure that uses EXECUTE AS OWNER to run the procedure in question.

Answer 2

In Oracle this is solvable through granting the owning schema rights, but I don't think this concept is mirrored in SQL Server.

We do have ability of assign rights to schema :

GRANT SELECT ON SCHEMA::SchemaA TO SomeRole;
go

If you have many users that are requiring rights to run the stored procedure, its better to create a role and then add users to the role and assign permissions to ROLE. Check my answer for Setting user permissions for different SQL Server schemas