Microsoft KB Archive/245039

= MOD2000: Cleaning Up Permissions After Removing Roles from the Solution Database =

Article ID: 245039

Article Last Modified on 7/31/2001

-

APPLIES TO


 * Microsoft Office 2000 Developer Edition

-



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

Additional query words: prb roles modPermissions delete

Keywords: kbprb kbworkflowdesigner KB245039

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.