MOD2000: Cleaning Up Permissions After Removing Roles from the Solution Database (245039)

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