Finding a form component permission in SQL

Stephen Cena

On prem, 9.01.12. A while back, I changed the permissions on some form components. These changes are working fine. I received a ticket to make changes to the settings & for some reason I cannot find where I made my changed. I'm pretty sure I did it using groups, but I can't seem to find the fields I'm looking for. Is there a simple T-SQL query I can run to find component level permissions? I looked at the AccountAuthorization table & can see some items but not the ones I'm looking for.

unknown

USE [CSI_App]
GO
/****** Object: StoredProcedure [dbo].[Rpt_UserAuthorizationSp] Script Date: 9/22/2021 9:34:11 AM ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DECLARE @StartingUsername UsernameType = NULL
, @EndingUsername UsernameType = NULL
, @StartingGroupName GroupnameType = NULL
, @EndingGroupName GroupnameType = NULL
, @UserStatus PasswordStatusType = NULL
, @UserEditLevel SmallintType = NULL
, @ShowOnlySuperUsers FlagNyType = NULL
, @ShowUserObjectAuthorizations FlagNyType = NULL
, @ShowUserRowAuthorizations FlagNyType = NULL
, @ShowUserGroups FlagNyType = NULL
, @ShowGroupObjectAuthorizations FlagNyType = NULL
, @ShowGroupRowAuthorizations FlagNyType = NULL
, @ShowUserModules FlagNyType = NULL
, @DisplayHeader FlagNyType = NULL
, @ObjectType AuthorizationObjectTypeType
, @StartingObjectName AuthorizationObjectNameType
, @EndingObjectName AuthorizationObjectNameType
, @pSite SiteType = NULL

--set @StartingUsername=N';'
set @UserStatus=9
set @UserEditLevel=9
set @ShowOnlySuperUsers=0
set @ShowUserObjectAuthorizations=1
set @ShowUserRowAuthorizations=1
set @ShowUserGroups=1
set @ShowGroupObjectAuthorizations=1
set @ShowGroupRowAuthorizations=1
set @ShowUserModules=1
set @DisplayHeader=0
set @ObjectType=3
set @StartingObjectName=';%WBDataViewSetup%'
--InvoiceListing, Config=COMPANY
--set @EndingObjectName=';Employees'
set @pSite=N';110_HQ'

-- Crystal reports has the habit of setting the isolation level to dirty
-- read, so we'll correct that for this routine now. Transaction management
-- is also not being provided by Crystal, so a transaction is started here.
BEGIN TRANSACTION
SET XACT_ABORT ON

IF dbo.GetIsolationLevel(N'UserAuthorization') = N'COMMITTED'
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
ELSE
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- A session context is created so session variables can be used.
DECLARE
@RptSessionID RowPointerType

EXEC dbo.InitSessionContextSp
@ContextName = 'Rpt_UserAuthorizationSp'
, @SessionID = @RptSessionID OUTPUT
, @Site = @pSite

DECLARE @UsersHeader TABLE (
UserId TokenType
, Username UsernameType
, UserDesc LongDescType
, Status PasswordStatusType
, EditLevel SmallintType
, SuperUserFlag FlagNyType
, GroupId TokenType
, GroupName GroupnameType
, GroupDesc LongDescType
)

DECLARE @Users TABLE (
UserId TokenType
, Username UsernameType
, UserDesc LongDescType
, Status PasswordStatusType
, EditLevel SmallintType
, SuperUserFlag FlagNyType
, GroupId TokenType
, GroupName GroupnameType
, GroupDesc LongDescType
-- UserAuthorization Objects
, Level NVARCHAR(20)
, ObjectType AuthorizationObjectTypeType
, ObjectName1 AuthorizationObjectNameType
, ObjectName2 AuthorizationObjectNameType
, ObjectName AuthorizationObjectNameType
, DeletePrivilege PrivilegeType
, EditPrivilege PrivilegeType
, ExecutePrivilege PrivilegeType
, InsertPrivilege PrivilegeType
, ReadPrivilege PrivilegeType
, UpdatePrivilege PrivilegeType
, BulkUpdatePrivilege PrivilegeType
-- UserAuthorization Rows
, CollectionName CollectionNameType
, Active ListYesNoType
, Filter CollectionFilterType
-- UserAuthorization Modules
, ModuleName LicenseModuleNameType
)

DECLARE
@UserId TokenType
, @Username UsernameType
, @UserDesc LongDescType
, @Status PasswordStatusType
, @EditLevel SmallintType
, @SuperUserFlag FlagNyType
, @GroupId TokenType
, @GroupName GroupnameType
, @GroupDesc LongDescType
, @Level NVARCHAR(20)
, @AuthorizationObjectType AuthorizationObjectTypeType
, @ObjectName1 AuthorizationObjectNameType
, @ObjectName2 AuthorizationObjectNameType
, @ObjectName AuthorizationObjectNameType
, @DeletePrivilege PrivilegeType
, @EditPrivilege PrivilegeType
, @ExecutePrivilege PrivilegeType
, @InsertPrivilege PrivilegeType
, @ReadPrivilege PrivilegeType
, @UpdatePrivilege PrivilegeType
, @BulkUpdatePrivilege PrivilegeType
, @CollectionName CollectionNameType
, @Active ListYesNoType
, @Filter CollectionFilterType
, @ModuleName LicenseModuleNameType
, @GLUserId TokenType
, @GLUsername UsernameType

DECLARE
@LowCharacter HighLowCharType
, @HighCharacter HighLowCharType
, @IncludeNullGroupNames FlagNyType
, @ShowObjects FlagNyType
, @ShowRows FlagNyType

SET @LowCharacter = dbo.LowCharacter()
SET @HighCharacter = dbo.HighCharacter()
SET @StartingUsername = ISNULL(@StartingUsername, @LowCharacter)
SET @EndingUsername = ISNULL(@EndingUsername, @HighCharacter)
SET @StartingObjectName = ISNULL(@StartingObjectName, @LowCharacter)
SET @EndingObjectName = ISNULL(@EndingObjectName, @HighCharacter)

SET @IncludeNullGroupNames = CASE WHEN @StartingGroupName IS NULL THEN 1 ELSE 0 END
SET @StartingGroupName = ISNULL(@StartingGroupName, @LowCharacter)
SET @EndingGroupName = ISNULL(@EndingGroupName, @HighCharacter)

SET @UserStatus = ISNULL(@UserStatus, 0)
SET @UserEditLevel = ISNULL(@UserEditLevel, 0)

SET @ShowOnlySuperUsers = ISNULL(@ShowOnlySuperUsers, 0)
SET @ShowUserObjectAuthorizations = ISNULL(@ShowUserObjectAuthorizations, 0)
SET @ShowUserRowAuthorizations = ISNULL(@ShowUserRowAuthorizations, 0)
SET @ShowUserGroups = ISNULL(@ShowUserGroups, 0)
SET @ShowGroupObjectAuthorizations = ISNULL(@ShowGroupObjectAuthorizations, 0)
SET @ShowGroupRowAuthorizations = ISNULL(@ShowGroupRowAuthorizations, 0)
SET @ShowUserModules = ISNULL(@ShowUserModules, 0)

SET @ShowObjects = CASE WHEN @ShowUserObjectAuthorizations = 1 OR @ShowGroupObjectAuthorizations = 1 THEN 1 ELSE 0 END
SET @ShowRows = CASE WHEN @ShowUserRowAuthorizations = 1 OR @ShowGroupRowAuthorizations = 1 THEN 1 ELSE 0 END

-- Create header
INSERT INTO @UsersHeader
SELECT
un.UserId
, un.Username
, un.UserDesc
, un.Status /* Status: 0 = Active; 1 = Locked Out; 2 = Disabled */
, un.EditLevel /* EditLevel: 0 = None; 1 = Basic; 2 = Full User; 3 = Site Developer; 4 = Vendor Developer */
, un.SuperUserFlag
, gn.GroupId
, gn.GroupName
, gn.GroupDesc
FROM UserNames un
LEFT OUTER JOIN UserGroupMap ugm
ON ugm.UserId = un.UserId
LEFT OUTER JOIN GroupNames gn
ON gn.GroupId = ugm.GroupId
LEFT JOIN AccountAuthorizations aa
ON aa.Id = un.UserId
AND (@ObjectType = 3 AND aa.ObjectType IN (0,2,4) /* ObjectType: 0 = Form; 1 = Component; 2 = IDO; 3 = File Server; */
OR (@ObjectType = 0 OR @ObjectType = 2 OR @ObjectType = 4) AND aa.ObjectType = @ObjectType AND aa.ObjectName1 BETWEEN @StartingObjectName AND @EndingObjectName)
WHERE un.Username BETWEEN @StartingUsername AND @EndingUsername
AND un.Status = CASE WHEN @UserStatus = 9 THEN un.Status ELSE @UserStatus END
AND un.EditLevel = CASE WHEN @UserEditLevel = 9 THEN un.EditLevel ELSE @UserEditLevel END
AND un.SuperUserFlag = CASE WHEN @ShowOnlySuperUsers = 1 THEN 1 ELSE un.SuperUserFlag END

DECLARE tmp_user_crs CURSOR LOCAL STATIC FOR
SELECT DISTINCT
tt.UserId
, tt.Username
, tt.UserDesc
, tt.Status
, tt.EditLevel
, tt.SuperUserFlag
FROM @UsersHeader tt
WHERE ( (tt.GroupName BETWEEN @StartingGroupName AND @EndingGroupName)
OR (tt.GroupName IS NULL AND @IncludeNullGroupNames = 1) )
ORDER BY tt.UserName

OPEN tmp_user_crs
FETCH NEXT FROM tmp_user_crs INTO
@UserId
, @Username
, @UserDesc
, @Status
, @EditLevel
, @SuperUserFlag

@fetch_status = 0
BEGIN

-- Create Details
INSERT INTO @Users
(
UserId
, Username
, UserDesc
, Status
, EditLevel
, SuperUserFlag
)
VALUES
(
@UserId
, @Username
, @UserDesc
, @Status
, @EditLevel
, @SuperUserFlag
)

-- Create Objects
IF @ShowObjects = 1
BEGIN
DECLARE tmp_Objects_crs CURSOR LOCAL STATIC FOR
SELECT
UserObjects.Level
, UserObjects.ObjectType
, UserObjects.ObjectName1
, UserObjects.ObjectName2
, UserObjects.ObjectName
, UserObjects.DeletePrivilege
, UserObjects.EditPrivilege
, UserObjects.ExecutePrivilege
, UserObjects.InsertPrivilege
, UserObjects.ReadPrivilege
, UserObjects.UpdatePrivilege
, UserObjects.BulkUpdatePrivilege
, UserObjects.GroupName
FROM
(SELECT
'USER' AS Level
, aa.ObjectType
, aa.ObjectName1 -- Not displayed
, aa.ObjectName2 -- Not displayed
, CASE
WHEN aa.ObjectType = 0 THEN aa.ObjectName1
WHEN aa.ObjectType = 1 THEN aa.ObjectName2
WHEN aa.ObjectType = 2 THEN aa.ObjectName1
END AS ObjectName
/* Privilege: 0 = Revoked; 1 = Granted */
, aa.DeletePrivilege
, aa.EditPrivilege
, aa.ExecutePrivilege
, aa.InsertPrivilege
, aa.ReadPrivilege
, aa.UpdatePrivilege
, aa.BulkUpdatePrivilege
, 'User' AS GroupName
FROM UserNames un
INNER JOIN AccountAuthorizations aa
ON aa.Id = un.UserId
AND aa.UserFlag = 1 /* UserFlag: 1 = Id relates to UserId; 0 = Id relates to GroupId */
AND (@ObjectType = 3 AND aa.ObjectType IN (0,1,2) /* ObjectType: 0 = Form; 1 = Component; 2 = Middleware */
OR (@ObjectType = 0 OR @ObjectType = 2) AND aa.ObjectType = @ObjectType AND aa.ObjectName1 BETWEEN @StartingObjectName AND @EndingObjectName)
WHERE un.UserId = @UserId
UNION ALL
SELECT
'GROUP' AS Level
, aa.ObjectType
, aa.ObjectName1 -- Not displayed
, aa.ObjectName2 -- Not displayed
, CASE
WHEN aa.ObjectType = 0 THEN aa.ObjectName1
WHEN aa.ObjectType = 1 THEN aa.ObjectName2
WHEN aa.ObjectType = 2 THEN aa.ObjectName1
END AS ObjectName
/* Privilege: 0 = Revoked; 1 = Granted */
, aa.DeletePrivilege
, aa.EditPrivilege
, aa.ExecutePrivilege
, aa.InsertPrivilege
, aa.ReadPrivilege
, aa.UpdatePrivilege
, aa.BulkUpdatePrivilege
, gn.GroupName
FROM UserNames un
INNER JOIN UserGroupMap ugm
ON ugm.UserId = un.userId
INNER JOIN GroupNames gn
ON gn.GroupId = ugm.GroupId
AND gn.GroupName BETWEEN @StartingGroupName AND @EndingGroupName
INNER JOIN AccountAuthorizations aa
ON aa.Id = gn.GroupId
AND aa.UserFlag = 0 /* UserFlag: 1 = Id relates to UserId; 0 = Id relates to GroupId */
AND (@ObjectType = 3 AND aa.ObjectType IN (0,1,2,4) /* ObjectType: 0 = Form; 1 = Component; 2 = IDO; 4 = File Server */
OR (@ObjectType = 0 OR @ObjectType = 2 OR @ObjectType = 4) AND aa.ObjectType = @ObjectType AND aa.ObjectName1 BETWEEN @StartingObjectName AND @EndingObjectName)
WHERE un.UserId = @UserId
) AS UserObjects
ORDER BY UserObjects.ObjectName1, UserObjects.GroupName, UserObjects.ObjectName2

OPEN tmp_Objects_crs
FETCH NEXT FROM tmp_Objects_crs INTO
@Level
, @AuthorizationObjectType
, @ObjectName1
, @ObjectName2
, @ObjectName
, @DeletePrivilege
, @EditPrivilege
, @ExecutePrivilege
, @InsertPrivilege
, @ReadPrivilege
, @UpdatePrivilege
, @BulkUpdatePrivilege
, @GroupName
@fetch_status = 0
BEGIN

INSERT INTO @Users
(
UserId
, Username
, UserDesc
, Status
, EditLevel
, SuperUserFlag
, GroupId
, GroupName
, GroupDesc
, Level
, ObjectType
, ObjectName1
, ObjectName2
, ObjectName
, DeletePrivilege
, EditPrivilege
, ExecutePrivilege
, InsertPrivilege
, ReadPrivilege
, UpdatePrivilege
, BulkUpdatePrivilege
, CollectionName
, Active
, Filter
, ModuleName
)
VALUES
(
@UserId
, @Username
, @UserDesc
, @Status
, @EditLevel
, @SuperUserFlag
, NULL
, @GroupName
, NULL
, @Level
, @AuthorizationObjectType
, @ObjectName1
, @ObjectName2
, @ObjectName
, @DeletePrivilege
, @EditPrivilege
, @ExecutePrivilege
, @InsertPrivilege
, @ReadPrivilege
, @UpdatePrivilege
, @BulkUpdatePrivilege
, NULL
, NULL
, NULL
, NULL
)

FETCH NEXT FROM tmp_Objects_crs INTO
@Level
, @AuthorizationObjectType
, @ObjectName1
, @ObjectName2
, @ObjectName
, @DeletePrivilege
, @EditPrivilege
, @ExecutePrivilege
, @InsertPrivilege
, @ReadPrivilege
, @UpdatePrivilege
, @BulkUpdatePrivilege
, @GroupName
END
CLOSE tmp_Objects_crs
DEALLOCATE tmp_Objects_crs
END

-- Create Rows
IF (@ObjectType = 3 OR @ObjectType = 2) AND @ShowRows = 1
BEGIN
DECLARE tmp_Rows_crs CURSOR LOCAL STATIC FOR
SELECT
UserRows.Level
, UserRows.UserId
, UserRows.Username
, UserRows.GroupId
, UserRows.GroupName
, UserRows.GroupDesc
, UserRows.CollectionName
, UserRows.Active
, UserRows.Filter
FROM
(SELECT
'USER' AS Level
, un.UserId
, un.Username
, gn.GroupId
, 'User' AS GroupName
, gn.GroupDesc
, ufil.CollectionName
, ufil.Active
, ufil.Filter
FROM UserNames un
INNER JOIN IDOFilter ufil -- user level row authorization
ON ufil.UserName = un.Username
AND ufil.CollectionName BETWEEN @StartingObjectName AND @EndingObjectName
LEFT OUTER JOIN GroupNames gn
ON gn.GroupName = ufil.GroupName
WHERE un.UserId = @UserId
UNION ALL
SELECT
'GROUP' AS Level
, un.UserId
, un.Username
, gn.GroupId
, gfil.GroupName
, gn.GroupDesc
, gfil.CollectionName
, gfil.Active
, gfil.Filter
FROM UserNames un
INNER JOIN UserGroupMap ugm
ON ugm.UserId = un.userId
INNER JOIN GroupNames gn
ON gn.GroupId = ugm.GroupId
AND gn.GroupName BETWEEN @StartingGroupName AND @EndingGroupName
INNER JOIN IDOFilter gfil -- group level row authorization
ON gfil.GroupName = gn.GroupName
AND gfil.UserName IS NULL
AND gfil.CollectionName BETWEEN @StartingObjectName AND @EndingObjectName
WHERE un.UserId = @UserId
UNION ALL
SELECT
'ALL' AS Level
, NULL AS UserId
, NULL AS Username
, NULL AS GroupId
, 'All' AS GroupName
, NULL AS GroupDesc
, fil.CollectionName
, fil.Active
, fil.Filter
FROM IDOFilter fil -- row authorizations for all users
WHERE fil.GroupName IS NULL
AND fil.UserName IS NULL
AND fil.CollectionName BETWEEN @StartingObjectName AND @EndingObjectName) AS UserRows
ORDER BY UserRows.CollectionName, UserRows.GroupName

OPEN tmp_Rows_crs
FETCH NEXT FROM tmp_Rows_crs INTO
@Level
, @GLUserId
, @GLUsername
, @GroupId
, @GroupName
, @GroupDesc
, @CollectionName
, @Active
, @Filter
@fetch_status = 0
BEGIN

INSERT INTO @Users
(
UserId
, Username
, UserDesc
, Status
, EditLevel
, SuperUserFlag
, GroupId
, GroupName
, GroupDesc
, Level
, ObjectType
, ObjectName1
, ObjectName2
, ObjectName
, DeletePrivilege
, EditPrivilege
, ExecutePrivilege
, InsertPrivilege
, ReadPrivilege
, UpdatePrivilege
, BulkUpdatePrivilege
, CollectionName
, Active
, Filter
, ModuleName
)
VALUES
(
@GLUserId
, @GLUsername
, @UserDesc
, @Status
, @EditLevel
, @SuperUserFlag
, @GroupId
, @GroupName
, @GroupDesc
, @Level
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, @CollectionName
, @Active
, @Filter
, NULL
)

FETCH NEXT FROM tmp_Rows_crs INTO
@Level
, @GLUserId
, @GLUsername
, @GroupId
, @GroupName
, @GroupDesc
, @CollectionName
, @Active
, @Filter
END
CLOSE tmp_Rows_crs
DEALLOCATE tmp_Rows_crs
END

-- Create Modules
IF @ShowUserModules = 1
BEGIN
DECLARE tmp_Modules_crs CURSOR LOCAL STATIC FOR
SELECT
um.ModuleName
FROM UserNames un
INNER JOIN UserModules um
ON um.UserId = un.UserId
WHERE un.UserId = @UserId

OPEN tmp_Modules_crs
FETCH NEXT FROM tmp_Modules_crs INTO
@ModuleName

@fetch_status = 0
BEGIN

INSERT INTO @Users
(
UserId
, Username
, UserDesc
, Status
, EditLevel
, SuperUserFlag
, GroupId
, GroupName
, GroupDesc
, Level
, ObjectType
, ObjectName1
, ObjectName2
, ObjectName
, DeletePrivilege
, EditPrivilege
, ExecutePrivilege
, InsertPrivilege
, ReadPrivilege
, UpdatePrivilege
, BulkUpdatePrivilege
, CollectionName
, Active
, Filter
, ModuleName
)
VALUES
(
@UserId
, @Username
, @UserDesc
, @Status
, @EditLevel
, @SuperUserFlag
, @GroupId
, @GroupName
, @GroupDesc
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, @ModuleName
)

FETCH NEXT FROM tmp_Modules_crs INTO
@ModuleName
END
CLOSE tmp_Modules_crs
DEALLOCATE tmp_Modules_crs
END

FETCH NEXT FROM tmp_user_crs INTO
@UserId
, @Username
, @UserDesc
, @Status
, @EditLevel
, @SuperUserFlag
END
CLOSE tmp_user_crs
DEALLOCATE tmp_user_crs

select @StartingUsername
SELECT distinct
CASE WHEN @ShowUserGroups = 1 THEN GroupName ELSE NULL END AS GroupName
--, CASE WHEN @ShowUserGroups = 1 THEN GroupDesc ELSE NULL END AS GroupDesc
, UserId
, Username
, UserDesc
--, Status
--, EditLevel
, SuperUserFlag
--, CASE WHEN @ShowUserGroups = 1 THEN GroupId ELSE NULL END AS GroupId
, CASE WHEN @ShowObjects = 1 THEN Level ELSE NULL END AS Level
, CASE WHEN @ShowObjects = 1 AND ObjectType = 0 THEN ObjectType ELSE NULL END AS ObjectType
--, CASE WHEN @ShowObjects = 1 THEN ObjectName1 ELSE NULL END AS ObjectName1
--, CASE WHEN @ShowObjects = 1 THEN ObjectName2 ELSE NULL END AS ObjectName2
, CASE WHEN @ShowObjects = 1 THEN ObjectName ELSE NULL END AS ObjectName
, ISNULL(CASE WHEN @ShowObjects = 1 THEN DeletePrivilege ELSE NULL END,0) AS DeletePrivilege
, ISNULL(CASE WHEN @ShowObjects = 1 THEN EditPrivilege ELSE NULL END,0) AS EditPrivilege
, ISNULL(CASE WHEN @ShowObjects = 1 THEN ExecutePrivilege ELSE NULL END,0) AS ExecutePrivilege
, ISNULL(CASE WHEN @ShowObjects = 1 THEN InsertPrivilege ELSE NULL END,0) AS InsertPrivilege
, ISNULL(CASE WHEN @ShowObjects = 1 THEN ReadPrivilege ELSE NULL END,0) AS ReadPrivilege
, ISNULL(CASE WHEN @ShowObjects = 1 THEN UpdatePrivilege ELSE NULL END,0) AS UpdatePrivilege
, ISNULL(CASE WHEN @ShowObjects = 1 THEN BulkUpdatePrivilege ELSE NULL END,0) AS BulkUpdatePrivilege
--, CASE WHEN @ShowRows = 1 THEN CollectionName ELSE NULL END AS CollectionName
--, CASE WHEN @ShowRows = 1 THEN Active ELSE NULL END AS Active
--, CASE WHEN @ShowRows = 1 THEN Filter ELSE NULL END AS Filter
--, CASE WHEN @ShowUserModules = 1 THEN ModuleName ELSE NULL END AS ModuleName
FROM @Users
where
1 = 1
--and objecttype = 0
and username in ('username1')
and ObjectName like @StartingObjectName
ORDER BY GroupName, UserName, ObjectName

COMMIT TRANSACTION
EXEC dbo.CloseSessionContextSp @SessionID = @RptSessionID