Database Schema Access

Question

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?

Answer 1

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