SQL user can only run proc, but that proc can do anything

Question

In SQL Server 2005, I want a user, called LimitedUser, to only be able to run one proc: GRANT EXEC ON [usp_RunETL] TO [LimitedUser]

However, that proc needs to be able to do everything -- UPDATE, DELETE, INSERT, EXEC.. everything. How do I do that without having to give all those permissions to LimitedUser?

Answer 1

LimitedUser only needs permission to run the sproc. They don't need any other permissions.

Answer 2

You only need to GRANT EXEC to the limited user. As long as the user has permissions to the stored procedure, it ignores/overrides permissions for operations inside of the procedure.

I would be very careful doing this though, this sounds like a big potential security hole. Typically you would use multiple procedures to have the flexibility to adjust permissions as necessary. I would recommend to assign the permissions to the procedure, or to the underlying tables and views, rather than decide that there will be only one super procedure.