Function: [AdventureWorks2016CTP3].[Security].[customerAccessPredicate]

CollapseAll image

Collapse image Function properties


 Name   Value 
 Schema   [Security] 
 Owner   [dbo] 
 Creation date   23.10.2015 
 Type   Inline 
 Is schema bound 
 Is deterministic   
 Encrypted   
 Implementation type   Transact SQL 
 ID   1678629023 

Collapse image Creation options


Name Value
QUOTED_IDENTIFIER ON
ANSI_NULLS ON

Collapse image Parameters


Name  Description DataType  Max length  ReadOnly
@TerritoryID   int 4  

Collapse image Recordset returned


Name  Datatype  Max length 
accessResult int 4

Collapse image Objects that [Security].[customerAccessPredicate] depends on


Object name Object type Dep level
[Security] Schema 1
[HumanResources].[Employee] Table 1
[Sales].[SalesPerson] Table 1
Total 3 object(s)

Collapse image SQL


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION Security.customerAccessPredicate(@TerritoryID int)
    RETURNS TABLE
    WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS accessResult
    FROM HumanResources.Employee e 
    INNER JOIN Sales.SalesPerson sp ON sp.BusinessEntityID = e.BusinessEntityID
    WHERE
        ( RIGHT(e.LoginID, LEN(e.LoginID) - LEN('adventure-works\')) = USER_NAME() AND sp.TerritoryID = @TerritoryID ) 
        OR IS_MEMBER('SalesManagers') = 1
        OR IS_MEMBER('db_owner') = 1
GO

Collapse image See also


List of functions