Roles needed for executing Stored Procedure in SQL Server Authentication

Question

I need to connect to SQL Server 2008 R2 database from a C# application. I need to use SQL Server authentication for the connection.

What are the roles and schemas needed for the user so that the user will be able to create/execute stored procedures ?

Note: The stored procedures will be in dbo schema.

Answer 1

Permissions for creating and executing procedures are documented under CREATE PROCEDURE and EXECUTE, respectively.

One important consideration is that users do not need to have permissions on the objects referenced in the procedure. You can read this in the documentation, but it's faster to test it yourself:

create table dbo.TestTable (col1 int)
go

create procedure dbo.TestProc
as select col1 from dbo.TestTable
go

grant execute on dbo.TestProc to UserWithNoPermissions
go

execute as user = 'UserWithNoPermissions';

-- this gives error 229 (SELECT permission denied)
select * from dbo.TestTable;

-- this works
execute dbo.TestProc;

revert;

Note that there are some exceptions: dynamic SQL executes in its own scope, so if your procedure uses it then the executing user will indeed need permission on the underlying objects.