MOD2000: Cleaning Up Permissions After Removing Roles from the Solution Database (245039)
The information in this article applies to:
- Microsoft Office 2000 Developer
This article was previously published under Q245039 SYMPTOMS
Once row-level permissions are enabled on an Access Workflow Designer solution, removing one or more roles from Microsoft SQL Server does not automatically remove the corresponding information from the modPermissions table of the solution database. You must remove the appropriate information from the modPermissions table manually.
In addition, re-creating a role with the same name as a previously removed role automatically provides the new role with all previous role permissions.
CAUSE
Access Workflow Designer preserves role information. Consequently, if you remove a role and then add the same role back, all the settings for workflow and row-level permissions are preserved. It preserves the row-level permissions settings because you may inadvertently orphan a row of data, making this row uneditable as when the only role that was able to modify the row is deleted. After the role is added back, you can adjust the row of data as needed.
RESOLUTION
To purge row-level and workflow permissions and clean up the modPermissions solution database table, add the following stored procedure to the solution database, and then run it:
CREATE PROCEDURE modCleanPermissionsafterRoleDeletion
AS
/*
modCleanPermissionsafterRoleDeletion
Does cleanup of modPermissions when a Role is deleted.
Because execute permissions and row level permissions are both
stored in modPermissions, use caution with this SP, as you may drop
permissions on a row level item, leaving it completely orphaned and
unable to be modified.
*/
DECLARE @RoleName sysname
DECLARE @ExecStr nvarchar(4000)
DECLARE RolesCursor CURSOR LOCAL FAST_FORWARD
FOR
SELECT DISTINCT modPermissions.RoleName AS Expr1
FROM sysusers RIGHT OUTER JOIN modPermissions ON
sysusers.name = modPermissions.RoleName
WHERE sysusers.name IS NULL
Open RolesCursor
Fetch RolesCursor into @RoleName
WHILE @@Fetch_Status <> -1
BEGIN
IF @@Fetch_Status <> -2
BEGIN
SELECT @ExecStr = 'DELETE FROM modPermissions WHERE Rolename = ''' + @RoleName + ''''
EXECUTE(@ExecStr)
END
FETCH RolesCursor INTO @RoleName
END
Modification Type: | Major | Last Reviewed: | 7/31/2001 |
---|
Keywords: | kbprb kbWorkFlowDesigner KB245039 |
---|
|