iVend Database Database : GetSecurityRoleSecurityFunction 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

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

SecSecurityFunction

Table

Defines a list of all the main modules on which the security needs to be applied. Eg.. Administration, Business partner etc.

1

SecSecurityRoleSecurityFunction

Table

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

1

CREATE PROCEDURE [dbo].[GetSecurityRoleSecurityFunction]

(

@pSecurityRoleKey   nvarchar(50)

)

AS

BEGIN

SET NOCOUNT ON

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

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

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

 , f.OrderBy, @pSecurityRoleKey 'SecurityRoleKey'

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

 , s.IsVisibleInMC

FROM SecSecurityFunction f LEFT OUTER JOIN SecSecurityRoleSecurityFunction s

ON  f.SecurityFunctionKey = s.SecurityFunctionKey

AND s.SecurityRoleKey = @pSecurityRoleKey

WHERE  f.isactive=1

ORDER BY f.OrderBy

RETURN

END