SQL Server - concedendo permissões para um esquema inteiro vs. objeto?

Você pode GRANTesquematizar permissões que são efetivas para tudo que existe e tudo que existirá nesse esquema.

Conceder permissões de esquema

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: <schema> TO <user>;

Além disso, se você quiser negar permissões em um determinado objeto dentro desse esquema, você pode fazê-lo.

Negando permissões de objeto

DENY INSERT ON OBJECT::<schema>.<object> TO <user>;

Para simplificar um pouco mais, você pode usar funções para fazer o trabalho que está procurando.

Depois de atribuir permissões à função, basta adicionar usuários à função. Dessa forma, você não precisa gerenciar permissões para usuários individuais. Os usuários herdam as permissões concedidas à função.

Abaixo está um exemplo para você começar:

 -- Create the database role
CREATE ROLE TableSelector AUTHORIZATION [dbo]
GO
 ---- Grant access rights to a specific schema in the database
GRANT 
      SELECT, INSERT, UPDATE, DELETE, ALTER 

ON SCHEMA::dbo
      TO TableSelector 
GO

-- Add an existing user to the new role created 
EXEC sp_addrolemember 'TableSelector', 'MyDBUser'
GO

-- Revoke access rights on a schema from a role 
DENY ALTER  -- you can customize here ...
ON SCHEMA::dbo
      TO TableSelector