Should a user be a schema owner in order to read, write, and execute SPs?
My webapp needs to read, write (INSERT, UPDATE, DELETE), and execute stored procedures on a SQL 2008 database with five schemas.
I created a user that authenticates through SQL, and granted the user db_datareader, db_datawriter, and db_procedureexec through Security -> Logins -> (Username) Properties -> User Mappings. I then configured the app to connect to the database using the username and the proper password, but upon attempting to execute a stored procedure, got this error:
The EXECUTE permission was denied on the object '(stored procedure name)', database '(new database)', schema '(schema 1)'.
Finding this user in the Security section of the database, I made it the owner of the five schemas in the DB.
Did I grant too many privileges? Should an application-level user be a schema owner in order to read, write, and exec procedures?
No, an app user should not need to be a schema owner in order to read, write and exec procedures.
You can say things like:
GRANT EXEC ON SCHEMA::whatever TO [user];
This will allow them to execute procedures in the [whatever] schema. In order to not require transitive privileges (e.g. say your procedures execute dynamic SQL), you can consider setting them to EXECUTE AS OWNER.
You don't want to grant an application user ownership of a database. This essentially gives them cart blanche. What you should do is to grant db_datareader and db_datawriter roles to the user, and grant execute on all applicable stored procedures and functions.