By default, no direct access is permitted to either tables or stored procedures. One must explicitly grant access in order for non-privileged users to have access. The best practice to simplify security administration is to grant permissions to roles, and control access via role membership.
If you want to restrict access only via stored procedures, take a look at ownership chaining (https://technet.microsoft.com/en-us/library/ms188676). This allows you to grant execute permissions to individual roles/users without allowing direct access to underlying tables as long as the object involves have the same owner (default is dbo).
Using ownership chaining in your scenario, you could grant only SELECT permission on the tables, with updates allowed only by users with execute permission on the update procs. No direct table modifications would be allowed.
Edit:
Below is an ownership chaining example. Note that neither user can update the table directly and the write user can update the table only using the stored procedure. The implication of ownership chaining is that when you grant execute permissions on a stored procedure, users can access all of the encapsulated objects without direct object permissions as long as the objects have the same owner.
--create objects
CREATE TABLE dbo.MyTable(
MyTableID int IDENTITY NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY
, SomeData int
);
GO
CREATE PROC dbo.UpdateMyTable
@MyTableID int
, @SomeData int
AS
UPDATE dbo.MyTable
SET SomeData = @SomeData
WHERE MyTableID = @MyTableID;
GO
--create roles
CREATE ROLE TableReaderRole;
CREATE ROLE TableWriterRole;
GO
--grant permissions to roles
GRANT SELECT ON dbo.MyTable TO TableReaderRole;
GRANT EXECUTE ON dbo.UpdateMyTable TO TableWriterRole;
GO
--create user with only read permissions
CREATE USER SomeReadOnlyUser FOR LOGIN SomeReadOnlyUser;
ALTER ROLE TableReaderRole
ADD MEMBER SomeReadOnlyUser;
GO
--create user with both read and writer permissions
CREATE USER SomeAllowedWriteUser FOR LOGIN SomeAllowedWriteUser;
ALTER ROLE TableReaderRole
ADD MEMBER SomeAllowedWriteUser;
ALTER ROLE TableWriterRole
ADD MEMBER SomeAllowedWriteUser;
GO
Be aware that it is the owner rather than the schema name that is relevant for ownership chaining. If the table and proc were in different schemas, the ownership chain would still be unbroken as long as the schemas had the same owner (AUTHORIZATION). The object owner is typically inherited from the schema owner.
