What is the difference between a securable and permission in SQL Server?

Question

In Microsoft SQL Server you have the possibility to set permissions at server and database level. You are also able to select a server login or database user and then set securables. I am wondering what choosing between these two strategies means from a security perspective. Does either permissions or securables have priority over the other?

In this article, a securable is defined as "resources that can be accessed by a principal", whereas permissions are defined as "Types of access granted on a securable to a specific principal". This sounds totally reasonable on paper. However, within Microsoft SQL Server Management Studio, I see that I can choose between "CustomDatabase -> Properties -> Permissions -> CustomUser" and "CustomDatabase -> Security -> Users -> CustomUser -> Properties -> Securables" when granting or denying a user the same action for the same database.

Let's say I would like a user to be able to read from all tables within a database. Should I grant the user SELECT permission in the database properties window or should I grant the user SELECT securable in the user properties window? What would be the difference in this case from choosing one over the other? Are there any other cases I should consider when choosing which user authorization strategy to pursue?

Any insight on this matter is highly appreciated, as I could not find any satisfying explanation anywhere else.

Answer 1

The permission on specific securable has precedence over entity which own this securable.

Which means:

If you grant select permission on the database level - that implies all tables/views in that database. Even if a table does not exist right now - the user will have select permission to it. If you deny select permission on a specific table - the user will not be able to read this table, while still having access to other tables.

If you grant select to all objects in database - user will have it. Changing permission on database or schema level will not affect actual access until you revoke permission from the table - in that case the permissions would be read from schema, then from database levels. A newly created table will be accessed by schema-database permission (until you set permissions on the table itself).

Read here for official explanation, it does read as legalese sometimes, but it is the most correct and full explanation of the process: https://learn.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-ver16

And returning to the title of the question:

• securable is an object, to which you can grant or deny access.
• permission is a type of access (read, update, delete, etc).