Row Assignments in a lookup table
CREATE TABLE [dbo].[predicate_tab](
[ACCT_NBR] [varchar](30) NULL,
[Netid] [varchar](30) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Orders](
[Supplier_Code] [int] NULL,
[Supplier_Name] [varchar](10) NULL,
[Orderdate] [datetime] NULL,
[OrderQuantity] [int] NULL,
[ACCT_NBR] [varchar](10) NULL
) ON [PRIMARY]
Create Function fn_securitypredicate (@ACCT_NBR sysname)
returns table
with Schemabinding
as
return select 1 as [fn_securityPredicate_result]
from dbo.predicate_tab
where (Netid = user_name() and @ACCT_NBR = ACCT_NBR)
OR IS_SRVROLEMEMBER(N'sysadmin') = 1
Create security Policy dbo.fn_security
add Filter Predicate
dbo.fn_securitypredicate(ACCT_NBR)
on dbo.orders