What is the difference between membership and securables? Does one override the other?

Question

When setting permissions for a database user, what is the difference between 'Membership' and 'Securables'?

From what I've read in the MSDN documentation (Securables and Database-Level Roles), I gather 'Membership' options are database-wide, whereas 'Securables' are more specific. If this is the case, which would be overridden if there was a conflict?

For example, if I wanted to set a user's access for a particular table to be read-only, but all others read-write, would I select 'db-datareader' and 'db-datawriter', and then specify a securable for the read-only table (with all options except 'Select' set to 'Deny')?

Answer 1

When setting permissions for a database user, what is the difference between 'Membership' and 'Securables'?

Membership is what the user belongs to... Since users are a database principal this could be database level roles, application roles, etc.

Securables are what the name implies, items that are authorized access to.

So, let's say we have a login which is a server level principal. The login can belong to a server level role.. it can be a member.. of that role. Then we give the server level role access to server level securables, such as and endpoint. Maybe we grant control endpoint to this role. Since the login has membership in this role it is granted the control endpoint permission against the endpoint securable.