SQL Server Row by Row Access

I have a table structured as so (Simplified)

Name, EMail, LastLoggedInAt

I have a user in SQL Server (RemoteUser) that should only be able to see data (Via a select query) where the LastLoggdInAt field is not null.

It looks like I can do this? Is it possible?

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

Since example code is a great way to show a concept, consider the following, with a LoginDetails table, and corresponding view:

CREATE TABLE dbo.LoginDetails
(
    Username nvarchar(100) NOT NULL
    , EmailAddress nvarchar(256) NOT NULL
    , LastLoggedInAt datetime NULL
);
GO

CREATE VIEW dbo.LoginDetailsView
AS
SELECT ld.Username
    , ld.EmailAddress
    , ld.LastLoggedInAt
FROM dbo.LoginDetails ld
WHERE ld.LastLoggedInAt IS NOT NULL;
GO

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

CREATE LOGIN RemoteUser 
WITH PASSWORD = '2q1345lkjsadfgsa0(*';

CREATE USER RemoteUser
FOR LOGIN RemoteUser
WITH DEFAULT_SCHEMA = dbo;

GRANT SELECT ON dbo.LoginDetailsView TO RemoteUser;

Now, we’ll insert two test rows:

INSERT INTO dbo.LoginDetails(Username, EmailAddress, LastLoggedInAt)
VALUES ('user x', 'x@y.com', NULL)
    , ('user y', 'y@y.com', GETDATE());

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

EXECUTE AS LOGIN = 'RemoteUser';

SELECT *
FROM dbo.LoginDetailsView;

╔══════════╦══════════════╦═════════════════════════╗
║ Username ║ EmailAddress ║     LastLoggedInAt      ║
╠══════════╬══════════════╬═════════════════════════╣
║ user y   ║ y@y.com      ║ 2018-02-15 07:36:54.490 ║
╚══════════╩══════════════╩═════════════════════════╝

SELECT *
FROM dbo.LoginDetails;

REVERT

Note the results from the view exclude the row where the LastLoggedInAt value is NULL, as required in your question.

The second SELECT statement against the underlying table returns an error:

Msg 229, Level 14, State 5, Line 28
The SELECT permission was denied on the object ‘LoginDetails’, database ‘tempdb’, schema ‘dbo’.

Cleanup:

DROP USER RemoteUser;
DROP LOGIN RemoteUser;
DROP VIEW dbo.LoginDetailsView;
DROP TABLE dbo.LoginDetails;

---

Alternately, if you have SQL Server 2016 or newer, you could use a row-level-security-predicate to prevent certain users seeing rows with a NULL LastLoggedInAt value. The Microsoft Docs for Row Level Security is here.

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

CREATE TABLE dbo.LoginDetails
(
    Username nvarchar(100) NOT NULL
    , EmailAddress nvarchar(256) NOT NULL
    , LastLoggedInAt datetime NULL
);
GO

CREATE LOGIN RemoteUser 
WITH PASSWORD = '2q1345lkjsadfgsa0(*';

CREATE USER RemoteUser
FOR LOGIN RemoteUser
WITH DEFAULT_SCHEMA = dbo;

GRANT SELECT ON dbo.LoginDetails TO RemoteUser;

Next, we insert a couple of sample rows. One row with a null LastLoggedInAt, and one with a non-null value for that column.

INSERT INTO dbo.LoginDetails(Username, EmailAddress, LastLoggedInAt)
VALUES ('user x', 'x@y.com', NULL)
    , ('user y', 'y@y.com', GETDATE());

Here, we’re creating a schema-bound table-valued-function that returns a row with either 0 or 1 depending on the value of the @LastLoggedInAt and @username variables that are passed into the function. This function will be used by a filter-predicate to eliminate the rows we want to hide from certain users.

CREATE FUNCTION dbo.fn_LoginDetailsRemoteUserPredicate
(
    @LastLoggedInAt datetime
    , @username sysname
)  
RETURNS TABLE  
WITH SCHEMABINDING  
AS  
    RETURN SELECT 1 AS fn_securitypredicate_result   
    WHERE (@username = N'RemoteUser' AND @LastLoggedInAt IS NOT NULL)
        OR @username <> N'RemoteUser';  
GO

This is the security filter that eliminates rows from SELECT statements ran against the dbo.LoginDetails table:

CREATE SECURITY POLICY LoginDetailsRemoteUserPolicy
ADD FILTER PREDICATE dbo.fn_LoginDetailsRemoteUserPredicate(LastLoggedInAt, USER_NAME())
ON dbo.LoginDetails
WITH (STATE=ON);

The filter above uses the dbo.fn_LoginDetailsRemoteUserPredicate function by passing in the name of the current user, along with the values from each row for the LastLoggedInAt column from the dbo.LoginDetails table.

If we query the table as a normal user:

SELECT *
FROM dbo.LoginDetails

we see all rows:

╔══════════╦══════════════╦═════════════════════════╗
║ Username ║ EmailAddress ║     LastLoggedInAt      ║
╠══════════╬══════════════╬═════════════════════════╣
║ user x   ║ x@y.com      ║ NULL                    ║
║ user y   ║ y@y.com      ║ 2018-02-15 13:53:42.577 ║
╚══════════╩══════════════╩═════════════════════════╝

However, if we test as the RemoteUser:

EXECUTE AS LOGIN = 'RemoteUser';

SELECT *
FROM dbo.LoginDetails

REVERT

we only see “valid” rows:

╔══════════╦══════════════╦═════════════════════════╗
║ Username ║ EmailAddress ║     LastLoggedInAt      ║
╠══════════╬══════════════╬═════════════════════════╣
║ user y   ║ y@y.com      ║ 2018-02-15 13:42:02.023 ║
╚══════════╩══════════════╩═════════════════════════╝

And, we clean up:

DROP SECURITY POLICY LoginDetailsRemoteUserPolicy;
DROP FUNCTION dbo.fn_LoginDetailsRemoteUserPredicate;
DROP USER RemoteUser;
DROP LOGIN RemoteUser;
DROP TABLE dbo.LoginDetails;

Be aware that schema-binding a function to the table in this way does make it impossible to modify the definition of the table without first dropping the filter predicate, and the dbo.fn_LoginDetailsRemoteUserPredicate function.