I have a database with multiple (10) schemas. I need a user to have access to two of them. If I grant access to the database and then GRANT SELECT to 2 schemas, is access to the other schemas DENY-ed by implication? Or must I grant permissions to those schemas and then DENY SELECT to the rest of them?
You should create roles and grant access to roles rather than granting users access directly to schema.
Once you assign permissions to the role, you can just add users to the role. This way you dont have to manage permissions for individual users. The users inherit permissions granted to role.
-- 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
see my answer to : Setting user permissions for different SQL Server schemas