Reporting: Deleting Users

1. Back up the database.

2. From Microsoft SQL Studio, run the script below against the CUIC database.

3. Expand Stored Procedures.

4. Locate, right-click, and execute the procedure named asp_DeleteUser. Then click Execute Stored Procedure.

5. Fill in the user name (domain\username)and execute the procedure.

SCRIPT

SET QUOTED_IDENTIFIER ON set ANSI_NULLS ON GO -- Delete all user related information for a given UserName (Domain\Name format) IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[asp_DeleteUser]') AND type in (N'P', N'PC')) EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [dbo].[asp_DeleteUser] @UserName varchar(150) AS BEGIN

DECLARE @errorFound tinyint Select @errorFound = 0

BEGIN TRANSACTION

   SELECT UserName FROM AA_Users WHERE UserName = @UserName

IF @@ERROR > 0 SET @errorFound = 1

DELETE AA_SharedObjects WHERE SharedWithID = @UserName AND PermissionInheritedFromID = (SELECT UserGroupID FROM AA_UserGroupMembership WHERE UserID=@UserName)

DELETE AA_UserGroupMembership WHERE UserID = @UserName and UserGroupID = (SELECT UserGroupID FROM AA_UserGroupMembership WHERE UserID=@UserName)

   DELETE AA_UsersAllowedReports WHERE UserID = @UserName

DELETE AA_CollectionsToUsers WHERE UserID = @UserName

DELETE UserRoles WHERE UserID = (SELECT UserID FROM Users WHERE UserName = @UserName) IF @@ERROR > 0 SET @errorFound = 1

DELETE FROM Users WHERE UserName = @UserName

DELETE FROM AA_Users WHERE UserName = @UserName

IF (@@ERROR > 0) OR (@errorFound > 0) BEGIN ROLLBACK END ELSE BEGIN COMMIT END END ' GO