T-SQL tip: Use a role for EXECUTE permissions

If you are in a high-security environment, this may not be the best tip for you, but if you’re in a situation like mine where you have a SQL user that is connecting to a database in order to EXECUTE stored procs, and you know that user needs to be able to EXECUTE every proc in that database, you can save a little time by creating a role for that:


CREATE ROLE usp_execute
GO

GRANT EXECUTE ON SCHEMA::dbo TO usp_execute
GO

This means that the user will be able to execute EVERY stored proc belonging to the schema dbo from this point forward; again, be cautious when using this.  Security models should not be taken lightly.

Share