What is the SQL Server Security Model

SQL Server line by line access

The SQL Server security model allows you to grant access to a view without granting access to the underlying tables.

Because sample code is a great way to show a concept, here are some things to keep in mind with a table and view:

We create a login and a user and then assign that user the rights to select rows from the view without having rights to view the table itself.

Now let's add two test lines:

This tests the security model. The first statement succeeds because it is selected from view, while the second statement fails because the user does not have direct access to the table.

╔══════════╦══════════════╦═══════════════════════ ══╗ ║ Username ║ EmailAddress ║ LastLoggedInAt ║ ╠══════════╬══════════════╬═════════════ ══════════ ══╣ ║ User y ║ [email protected] ║ 02/15/2018 07: 36: 54.490 ║ ╚══════════╩══════ ════════╩═══════════════════════ ══╝

Notice that the results exclude from view the row that the value is on, as required by your question.

The second statement on the underlying table returns an error:

Message 229, Level 14,
Status 5, line 28 The SELECT authorization was denied for the object 'LoginDetails', database 'tempdb', schema 'dbo'.

To clean up:

Alternatively, if you have SQL Server 2016 or later, you can use a row-level security predicate to prevent certain users from seeing rows with a NULL value.

First we create the table, a login, a user for this login and grant access to the table:

Next, let's add some example lines. One row with a zero and one with a non-zero value for this column.

Here we create a schema-bound table-valued function that returns a row with 0 or 1 depending on the value of the variable and the variable passed to the function. This function is used by a filter predicate to remove the rows that we want to hide from certain users.

This is the security filter that removes lines from statements executed on the table:

The above filter uses the function by passing the name of the current user along with the values ​​from each row for the column from the table.

If we query the table as a normal user:

we see all the lines:

╔══════════╦══════════════╦═══════════════════════ ══╗ ║ Username ║ EmailAddress ║ LastLoggedInAt ║ ╠══════════╬══════════════╬═════════════ ══════════ ══╣ ║ User x ║ [email protected] ║ NULL ║ ║ User y ║ [email protected] ║ 2018-02-15 13: 53: 42.577 ║ ╚═══ ═══════╩══════════════╩═══════════════════════ ══╝

However, if we test the following:

We only see "valid" lines:

╔══════════╦══════════════╦═══════════════════════ ══╗ ║ Username ║ EmailAddress ║ LastLoggedInAt ║ ╠══════════╬══════════════╬═════════════ ══════════ ══╣ ║ User y ║ [email protected] ║ 02/15/2018 13: 42: 02.023 ║ ╚══════════╩══════ ════════╩═══════════════════════ ══╝

And we clean up:

Note that the schema that binds a function to the table in this way makes it impossible to change the definition of the table without first clearing the filter predicate and the function.