USE [ION_Network]
GO

/****** Object:  StoredProcedure [dbo].[spAC_GetUserRole]    Script Date: 11/28/2017 10:48:34 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--*****************************************************************************
/* ##BD_BEGIN dbo.spAC_GetUserRole

<P><I>For more information, follow the Source link at the top of the page.</I></P>
##BD_END */
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/AC_AccessControl/StoredProcedures/01.spAC_GetUserRole.sql#2 $
-- 
--*****************************************************************************
ALTER PROCEDURE [dbo].[spAC_GetUserRole]
(
	@aUserID		UserID,
	@returnRoleID 	SMALLINT OUT
)
AS
BEGIN

	-- Get User's role ID
	DECLARE @userRoleID SMALLINT
	SET @userRoleID=(SELECT RoleID FROM Users WHERE UserID=@aUserID)

	-- If user not found, return NULL
	IF @userRoleID IS NULL
	BEGIN
		SET @returnRoleID = NULL
		RETURN 0
	END

	-- Get the most permissive group role ID
	DECLARE @bestGroupRoleID SMALLINT
	SET @bestGroupRoleID=
		(
			SELECT MIN(RoleID) 
			FROM UsersGroups 
			WHERE UserGroupID IN 
			(
				SELECT TrusteeID FROM dbo.udfAC_GetAllTrustees(@aUserID)
			)
		)

	-- Guard against some bad group return value
	IF @bestGroupRoleID IS NULL
		SET @bestGroupRoleID = @userRoleID
		
	-- Find largest of @userRoleID and @bestGroupRoleID
	IF @userRoleID >= @bestGroupRoleID
		SET @returnRoleID = @userRoleID
	ELSE
		SET @returnRoleID = @bestGroupRoleID
END

GO


