Use master;
Go

-- New database for testing
Create Database TestRoles;
Go

-- Logins must be created in master database
CREATE LOGIN LoanClerk
    WITH PASSWORD = 'P@ssw0rd',
    Default_Database = TestRoles;
CREATE LOGIN SalesMgr
    WITH PASSWORD = 'P@ssw0rd',
    Default_Database = TestRoles;
Go

-- switch to new database
Use TestRoles;
Go

-- Create database roles
Create Role LoanClerks;
Create Role SalesMgrs;
Go

-- Create databse users
Create User LoanClerk For Login LoanClerk;
Create User SalesMgr For Login SalesMgr;
Go

-- Add Users to roles
Exec sp_addrolemember @rolename = N'Loanclerks',
    @membername = N'LoanClerk';

Exec sp_addrolemember @rolename = N'SalesMgrs',
    @membername = N'SalesMgr';
Go

-- create the Loans table
-- Due date is 7 days from today by default
-- Role that can see the record defaults to public if not specified
    -- May want to use db_owner for more security
-- Stores the user name of the inserter, gives you the ability to allow the
    -- inserter to see data they inserted even if not in the specified row
Create Table dbo.Loans (
    LoanID int identity(1, 1) not null primary key,
    ISBN varchar(10) not null,
    DueDate datetime not null default(DATEADD(day, 7, GETDATE())),
    MemberRole sysname not null default('public'),
    InsertedBy nvarchar(256) not null default(SUSER_SNAME())
    );
Go

CREATE VIEW dbo.vLoans
AS
SELECT ISBN, DueDate
FROM Loans AS l
WHERE IS_MEMBER(MemberRole) = 1
Or InsertedBy = SUSER_SNAME();
GO

-- Grant select to the view
GRANT SELECT On dbo.VLoans To LoanClerks;
GRANT SELECT On dbo.VLoans To SalesMgrs;
Go

-- Add some sample data
Insert Into dbo.Loans (ISBN, MemberRole)
Values ('abcdef', 'LoanClerks'),
    ('bcdefg', 'SalesMgrs'),
    ('123456', 'public'), -- public = everyone with access
    ('098765', 'Librarian') -- only inserter should see since no role of Librarian
Go

-- Should only see rows with ISBN = abcdef and 123456
EXECUTE AS USER = 'LOANCLERK';

SELECT * FROM dbo.vLoans;

REVERT;
Go

-- Should only see rows with ISBN = bcdefg and 123456
EXECUTE AS USER = 'SalesMgr';

SELECT * FROM dbo.vLoans;

REVERT;
Go

-- Should see all rows inserted themself
Select *
From dbo.vLoans
Go

-- Drop database
Use master;
Go

Drop Database Testroles;
Go

-- Drop logins
Drop Login LoanClerk;
Drop Login SalesMgr;