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