Database user permissions: GRANT VIEW DEFINITION but DENY on some object types

I'm trying to figure out a combination of permissions that will allow a partner to see basic db schema but not expose objects including code. So,

• VIEW and SELECT on TABLES (including keys)

• Prevent/DENY on Views, Stored Procs, Functions.

I've thought about going through and denying on individual objects, but this won't protect any that are newly created.

Added consideration - I need to get this to work in SQL Azure.

You can create a role and then grant / revoke permissions to it. Any user that is a part of the role will inherit the permissions.

Below is an example to get you started :

-- Create the database role
CREATE ROLE TableSelector AUTHORIZATION [dbo]
GO
 ---- Grant access rights to a specific schema in the databas
GRANT 
      SELECT 

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

StackExchange Database Administrators Q#39619, answer score: 3