SQL Server Security

Question

In order for a user to be able to run a SQL Server Stored Procedure in a user database what's the minimum security access required?

Answer 1

The user needs EXECUTE permission on the procedure. And CONNECT permission to the database. (That is, have to access to the database in the first place.)

This is under the assumption that the stored procedure is only performing plain DML: SELECT, INSERT, UPDATE, DELETE and MERGE. If the procedure performs actions like, say, creating tables, the user needs explicit permission to those actions.

Answer 2

Adding to Erland's answer, no additional permissions are needed as long as 1) the objects used by the stored procedure exist in the same database, 2) belong to the same schema (e.g. dbo), and 3) are accessed via static DML. Additional permissions and/or security configuration are needed for cross-database access and dynamic SQL.

Answer 3

Thank you that's what I was missing.