CREATE TABLE [Authorized] (
[TargetTable] [nvarchar] (128) NOT NULL,
[DataRowPrimaryKey] [nvarchar] (128) NOT NULL,
[PermittedSuser_Sname] [nvarchar] (128) NOT NULL,
CONSTRAINT [PK_Authorized] PRIMARY KEY  CLUSTERED 
([TargetTable],[PermittedSuser_Sname],[DataRowPrimaryKey]))

CREATE VIEW dbo.view_rs_CaseManagement
AS SELECT
dbo.Patients.id, dbo.Patients.lname, dbo.Patients.fname, dbo.Patients.PatientID, dbo.CaseManagement.CaseManagementID, dbo.CaseManagement.CaseID, dbo.CaseManagement.cm_notes, dbo.CaseManagement.cm_call_back, dbo.CaseManagement.cm_date
FROM 
dbo.Authorized INNER JOIN dbo.CaseManagement ON dbo.Authorized.DataRowPrimaryKey = dbo.CaseManagement.CaseManagementID INNER JOIN
dbo.Cases ON dbo.CaseManagement.CaseID = dbo.Cases.CaseID INNER JOIN
dbo.Patients ON dbo.Cases.PatientID = dbo.Patients.PatientID
WHERE 
(dbo.Authorized.PermittedSuser_Sname = SUSER_SNAME()) AND (dbo.Authorized.TargetTable = 'CaseManagement')
CREATE VIEW dbo.view_rs_Cases
AS SELECT
dbo.Patients.id, dbo.Patients.lname, dbo.Patients.fname, dbo.Patients.PatientID, dbo.Cases.CaseID, dbo.Cases.case_sub_type, dbo.Cases.case_first_visit, dbo.Cases.case_authorized_by, dbo.Cases.case_occupation, dbo.Cases.case_job_code
FROM
dbo.Authorized INNER JOIN dbo.Cases ON dbo.Authorized.DataRowPrimaryKey = dbo.Cases.CaseID INNER JOIN dbo.Patients ON dbo.Cases.PatientID = dbo.Patients.PatientID
WHERE
(dbo.Authorized.PermittedSuser_Sname = SUSER_SNAME()) AND (dbo.Authorized.TargetTable = 'Cases')
CREATE VIEW dbo.view_rs_Patients
AS SELECT
dbo.Patients.id, dbo.Patients.lname, dbo.Patients.fname, dbo.Patients.PatientID, dbo.Patients.phone, dbo.Patients.address, dbo.Patients.city, dbo.Patients.state, dbo.Patients.zip
FROM
dbo.Authorized INNER JOIN dbo.Patients ON dbo.Authorized.DataRowPrimaryKey = dbo.Patients.PatientID
WHERE     
(dbo.Authorized.PermittedSuser_Sname = SUSER_SNAME()) AND (dbo.Authorized.TargetTable = 'Patients')

INSERT INTO Authorized
(TargetTable, DataRowPrimaryKey, PermittedSuser_Sname)
SELECT     
'CaseManagement' AS TargetTable, 
CaseManagementID AS DataRowPrimaryKey, 
lname AS 'PermittedSuser_Sname'
FROM  dbo.view_CaseManagement

INSERT INTO Authorized
(TargetTable, DataRowPrimaryKey, PermittedSuser_Sname)
SELECT     
'CaseManagement' AS TargetTable, 
CaseManagementID AS DataRowPrimaryKey, 
'sa' AS 'PermittedSuser_Sname'
FROM  dbo.view_CaseManagement

INSERT INTO Authorized
(TargetTable, DataRowPrimaryKey, PermittedSuser_Sname)
SELECT     
'Cases' AS TargetTable, 
CaseID AS DataRowPrimaryKey, 
lname AS 'PermittedSuser_Sname'
FROM  dbo.view_Cases

INSERT INTO Authorized
(TargetTable, DataRowPrimaryKey, PermittedSuser_Sname)
SELECT     
'Cases' AS TargetTable, 
CaseID AS DataRowPrimaryKey, 
'sa' AS 'PermittedSuser_Sname'
FROM  dbo.view_Cases

INSERT INTO Authorized
(TargetTable, DataRowPrimaryKey, PermittedSuser_Sname)
SELECT     
'Patients' AS TargetTable, 
PatientID AS DataRowPrimaryKey, 
lname AS 'PermittedSuser_Sname'
FROM dbo.view_Patients

INSERT INTO Authorized
(TargetTable, DataRowPrimaryKey, PermittedSuser_Sname)
SELECT     
'Patients' AS TargetTable, 
PatientID AS DataRowPrimaryKey, 
'sa' AS 'PermittedSuser_Sname'
FROM dbo.view_Patients