Menu

Action Form Notes

Action Form is a module developed by PlantAnApp for the DNN Content Management System and Development Platform. The developers at LetItShine.biz have used the Action Form module for countless projects and solutions for a number of years. The following is an modest collection of notes that we have started to record about techniques to take advantage of the strengths of this module. We record this here not to become a secondary help system for Action Form users or DNNSharp. But if you can find something here that helps you, that makes us happy too.

Return to the list
Mark Buelsing
/ Categories: Action Form

HasRole SP

This script will return true or false after testing that a userid is in a given role or is superuser

 


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE procedure [dbo].[_LIS_HasRole]

    @UserID int 
    ,@RoleName nvarchar(150) 

AS

    DECLARE @CheckUserID int
    DECLARE @HitCount int = 0

    SET @CheckUserID = @UserID
    IF @CheckUserID <> 0 
    BEGIN
    --if user is superuser
    SET @HitCount = (SELECT IsSuperUser FROM Users WHERE UserID = @CheckUserID)

     -- if user is administrator
    SET @HitCount = @HitCount + (
    SELECT COUNT(*)
    FROM UserRoles INNER JOIN
    Roles ON UserRoles.RoleID = Roles.RoleID INNER JOIN
    Users ON UserRoles.UserID = Users.UserID
    WHERE Roles.Rolename = 'Administrators' AND Users.UserID = @CheckUserID)

    --is user in the queried role
    SET @HitCount = @HitCount + (
    SELECT COUNT(*)
    FROM UserRoles INNER JOIN
    Roles ON UserRoles.RoleID = Roles.RoleID INNER JOIN
    Users ON UserRoles.UserID = Users.UserID
    WHERE Roles.Rolename = @RoleName AND Users.UserID = @CheckUserID)

--return the answer

    SELECT CAST(CASE WHEN @HitCount > 0 THEN 'True' ELSE 'False' END AS nvarchar(10)) AS HasRole
END
ELSE
    SELECT 'False' AS HasRole

 

____________________________

 

Or if you need to make this determination without a stored procedure

 

    DECLARE @UserID int =11
    DECLARE @CheckUserID int
    DECLARE @HitCount int = 0
    DECLARE @RoleName nvarchar(30) = 'Administrators'

    SET @CheckUserID = @UserID
    IF @CheckUserID <> 0 
    BEGIN
    --if user is superuser
    SET @HitCount = (SELECT IsSuperUser FROM Users WHERE UserID = @CheckUserID)

     -- if user is administrator
    SET @HitCount = @HitCount + (
    SELECT COUNT(*)
    FROM UserRoles INNER JOIN
    Roles ON UserRoles.RoleID = Roles.RoleID INNER JOIN
    Users ON UserRoles.UserID = Users.UserID
    WHERE Roles.Rolename = 'Administrators' AND Users.UserID = @CheckUserID)

    --is user in the queried role

  SET @HitCount = @HitCount + (
    SELECT COUNT(*)
    FROM UserRoles INNER JOIN
    Roles ON UserRoles.RoleID = Roles.RoleID INNER JOIN
    Users ON UserRoles.UserID = Users.UserID
    WHERE Roles.Rolename = @RoleName AND Users.UserID = @CheckUserID)

--return the answer

    SELECT CAST(CASE WHEN @HitCount > 0 THEN 'True' ELSE 'False' END AS nvarchar(10)) AS HasRole
END
ELSE
    SELECT 'False' AS HasRole

 

_____________________

Here is another way to see if user has permisison

 

SELECT CAST(CASE WHEN COUNT(*) > 0 THEN 'CanEdit' ELSE 'CannotEdit' END AS nvarchar(15)) AS 'HasRole'
FROM UserRoles INNER JOIN
Roles ON UserRoles.RoleID = Roles.RoleID INNER JOIN
Users ON UserRoles.UserID = Users.UserID
WHERE Roles.Rolename IN ('Newsletter Manager', 'Administrators') AND Users.UserID = '[User:UserID]'

 

Previous Article Remove the ActionGrid URL Parameters
Next Article Push a Grid Selection to a Form
Print
2016 Rate this article:
No rating
Tags: HasRole

Leave a comment

This form collects your name, email, IP address and content so that we can keep track of the comments placed on the website. For more info check our Privacy Policy and Terms Of Use where you will get more info on where, how and why we store your data.
Add comment

Links