iVend Database Database : GetSecurityRoleSecuritySubFunction Stored Procedure

Creation Date

4/23/2019 7:06 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pSecurityRoleKey

In

Reference key of the Security Role

VarWChar

50

@pSecurityFunctionKey

In

Reference key of the Security Function

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

SecSecurityRoleSecuritySubFunction

Table

This table defines the security rights on the sub modules granted to a specific security role.

1

SecSecuritySubFunction

Table

Defines a list of all the sub modules under the main module on which the security needs to be applied.

1

CREATE PROCEDURE [dbo].[GetSecurityRoleSecuritySubFunction]

(

@pSecurityRoleKey   nvarchar(50),

@pSecurityFunctionKey   nvarchar(50)

)

AS

BEGIN

SET NOCOUNT ON

SELECT f.Id, f.Description, IsNull(s.AccessLevel, '0') 'AccessLevel'

 , IsNull(f.SecuritySubFunctionKey, '0') 'SecuritySubFunctionKey'

 , IsNull(f.SecurityFunctionKey, '0') 'SecurityFunctionKey'

 , IsNull(s.SecurityRoleSecuritySubFunctionKey, '0') 'SecurityRoleSecuritySubFunctionKey'

 , f.OrderBy, IsNull(s.SecurityRoleKey, '0') 'SecurityRoleKey'

 , isnull(f.ShowInLoyaltyStandalone, '0') 'ShowInLoyaltyStandalone'

 , f.IsBooleanType

 , s.IsVisibleInMC

FROM SecSecuritySubFunction f LEFT OUTER JOIN SecSecurityRoleSecuritySubFunction s

ON  f.SecuritySubFunctionKey = s.SecuritySubFunctionKey

AND IsNull(s.SecurityRoleKey, '0') = @pSecurityRoleKey

WHERE  f.isactive=1

And f.SecurityFunctionKey = @pSecurityFunctionKey

ORDER BY f.OrderBy

RETURN

END