IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'dbo.CP_DB_CloneLoginAndPerm') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
    DROP PROCEDURE dbo.CP_DB_CloneLoginAndPerm 
END
GO

CREATE PROCEDURE dbo.CP_DB_CloneLoginAndPerm
  @NewLogin sysname,
  @NewLoginPwd NVARCHAR(MAX),
  @WindowsLogin CHAR(1),
  @LoginToClone sysname,
  @pDBName sysname
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Return int;
DECLARE @SQL nvarchar(MAX);

BEGIN TRAN;

EXEC @Return = dbo.CP_DB_CloneLogin 
@NewLogin = @NewLogin, 
@NewLoginPwd = @NewLoginPwd, 
@WindowsLogin = @WindowsLogin, 
@LoginToClone = @LoginToClone;

IF (@Return <> 0)
BEGIN
	ROLLBACK TRAN;
	RAISERROR('Exiting because login could not be created', 16, 1);
	RETURN(1);
END

SET @SQL= 'ALTER LOGIN '+ @NewLogin +' WITH PASSWORD = '''+ @NewLoginPwd +''';'
EXEC @Return = sp_executesql @SQL;
IF (@Return <> 0)
BEGIN
	ROLLBACK TRAN;
	RAISERROR('Error updating login password', 16, 1);
	RETURN(1);
END

EXEC @Return = dbo.CP_DB_CreateUser 
	@NewLogin = @NewLogin, 
	@DBName = @pDBName;

IF (@Return <> 0)
BEGIN
	ROLLBACK TRAN;
	RAISERROR('Exiting because user could not be created.', 16, 1);
	RETURN(1);
END;

EXEC @Return = dbo.CP_DB_GrantUserRoleMember
	@NewLogin = @NewLogin, 
	@LoginToClone = @LoginToClone, 
	@DBName = @pDBName;

IF (@Return <> 0)
BEGIN
	ROLLBACK TRAN;
	RAISERROR('Exiting because role meberships could not be granted.', 16, 1);
	RETURN(1);
END;

EXEC @Return = dbo.CP_DB_ClonePerms
	@NewLogin = @NewLogin, 
	@LoginToClone = @LoginToClone, 
	@DBName = @pDBName;

IF (@Return <> 0)
BEGIN
	ROLLBACK TRAN;
	RAISERROR('Exiting because permisions could not be created.', 16, 1);
	RETURN(1);
END;



COMMIT TRAN;
END;
