As long as you have your database organized in such a way that there is an account that is the owner of the tables and other objects, and your application connects using a separate account you can do what you want.
If you have tables and procedures organized in separate owners, you fist have to grant the required tables to the procedure owner (directly granted). Next you can grant the execute to a role or a user.
As soon as you allow connections to an object owner your security is broken since in that case you can always use all the objects of the owner (you are owner so you can use it).
From the documentation (GRANT Schema Permissions):
GRANT SELECT, DELETE -- , etc.
ON SCHEMA::schema_name
TO user_name;
For certain permissions they need to be granted at both the database level and the schema level. For example, you can give a user CREATE TABLE permissions at the database level, but that doesn't just allow them to create tables in any schema. Here is an example you can work through:
-- create a server-level SQL auth login
CREATE LOGIN DouglasExample WITH PASSWORD = 'x',
CHECK_POLICY = OFF;
-- create an empty database and add a schema
CREATE DATABASE Douglas;
GO
USE Douglas;
GO
CREATE SCHEMA foo;
GO
-- create a user linked to the login
CREATE USER DouglasExample FROM LOGIN DouglasExample;
GO
-- grant the user the ability to create tables
GRANT CREATE TABLE TO DouglasExample;
GO
-- grant them explicit permissions on the schema only
GRANT SELECT, INSERT, DELETE, ALTER, EXECUTE, CONTROL
ON SCHEMA::foo TO DouglasExample;
GO
-- now try to create tables in foo and in dbo
EXECUTE AS USER = N'DouglasExample';
GO
CREATE TABLE foo.what(id INT); -- succeeds
GO
CREATE TABLE dbo.who(id INT); -- fails, not in schema foo
-- CREATE TABLE who(id INT); would also fail with same error
-- unless DouglasExample had foo as their default_schema
GO
REVERT;
GO
The attempt in dbo fails with:
Msg 2760, Level 16, State 1
The specified schema name "dbo" either does not exist or you do not have permission to use it.
-- clean up:
USE master;
GO
DROP DATABASE Douglas;
GO
DROP LOGIN DouglasExample;
As a side note, please observe how important it is to always explicitly state the schema name when creating/referencing all objects.
Best Answer
You are correct that there is no way to grant a user create/drop/etc permissions on an entire schema. I suggest you look into proxy authentication. This basically involves altering user A to allow user B to proxy as A:
Then the connection uses user B's authentication, but gets the permissions of user A.
This question was somewhat covered by my more specific question here.
Note on Roles: Roles work well for giving Object Privileges to another user since the privileges are tied to a specific object. While Roles can grant System Privileges, they apply either to the users own schema or to the entire database and therefore can't apply to another schema. For example, the user B could be granted
CREATE TABLEwhich would allow it to create tables in its own schema orCREATE ANY TABLEwhich would allow it to create tables in any schema. These permissions could be granted directly or through a role, but the former wouldn't allow create privileges in the A schema. The latter would, but would also allow create privileges in any schema including sys, which would be a security concern.