I have created a database level role: NonBillingRole.
CREATE ROLE NonBillingRole
then granted it all access and revoked/denyed access of Billing table through following command:
REVOKE SELECT, INSERT, UPDATE, DELETE ON TblBilling TO NonBillingRole
DENY SELECT, INSERT, UPDATE, DELETE ON TblBilling TO NonBillingRole
Then I have added a user to this role.
This works fine if I directly tries to select or modify the table TblBilling. But the user can select and modify the table through any stored procedure.
My goal is to create a role that have all access to the database including executing the stored procedure but do not have direct or indirect access (even through stored procedure) to the table TblBilling.
Deny's are higher than allows - but procedures can have impersonation abilities, or run as owner, which can circumvent your intentions.
When you add access to the role for table, and DENY that will put some higher level access constraints in place, but it can also cause issues down the road for Views, Stored Procedures, and Functions. I rarely use DENY as it causes unseen issues with getting to data.
Make sure the user you have is public only, and make a role for only the access you want them to have to the objects you intend for them to use. If an application controls access, you will be hard pressed to constrain access in the database without consequences in the application.