1

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?

3

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

  • 1
    Note REVOKE and DENY are different. REVOKE removes a GRANT, DENY overrides it. So you can GRANT the whole schema and DENY one table, but not REVOKE it. – David Browne - Microsoft May 31 '18 at 22:07
  • As an Accidental DBA I sincerely appreciate this advice, but my base question remains unanswered. Does the "Principle of Least Privilege" apply here, or do I totally misunderstand the concept? Let me rephrase. Using the example above, if I grant SELECT to 'TableSelector' for one schema, is access to the other schema denied? Or do I need to explicitly DENY access to those schema? – Steve_Malcolm Jun 1 '18 at 13:47
  • 1
    If you grant select on one schema then the role wont have access to other schemas. – Kin Shah Jun 1 '18 at 14:08
  • Thanks Kin, I sincerely appreciate this. I will be implementing the roles as you suggest. – Steve_Malcolm Jun 1 '18 at 15:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.