Remove SQL Replication in Publisher and Subscriber after changing server names

Remove SQL Replication even when the publisher or Subscriber Server is not available

Existing server/computer are renamed and given new names. If you are trying to update the Instance name of  SQL Server. it wont allow you to change the name.  And if you try to remove subscriber and publishers Forcefully  also will through below error.

OLE DB provider "SQLNCLI10" for linked server "repl_distributor" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "repl_distributor" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 65535, Level 16, State 1, Line 0

SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. 

Solution to drop subscriber and publisher are as below (As per MS This procedure should be used only if other methods of removing replication objects have failed)

Declare @repdb as sysname

Set @repdb = 'MyPublisherDatabase' 

EXEC sp_removedbreplication @repdb

Go 

Grant VIEW DEFINITION to all databases stored procedure in SQL Server

/*
Change the user name name in 2nd line and
Replace print @sqlstmt  with exec @sqlstmt to grant VIEW DEFINITION to all databases stored procedure in SQL Server
*/



DECLARE @userName varchar(200)
SET @userName = 'corp\domainuser'

DECLARE @name varchar(50)
DECLARE @spname varchar(50)
DECLARE @schemaname varchar(50)
DECLARE @sqlstmt nvarchar(300)
DECLARE @sqlsel varchar(300)


DECLARE db_cursor CURSOR FOR
SELECT
  name
FROM MASTER.sys.databases
WHERE state_desc = 'ONLINE'
AND is_in_standby = 0
AND name NOT IN ('master', 'model', 'msdb', 'tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
  --Print @name  
  SET @sqlsel = 'DECLARE spro_cursor CURSOR FOR SELECT  SPECIFIC_NAME, SPECIFIC_SCHEMA  from [' + @name + '].information_schema.routines where routine_type = ''PROCEDURE'' and routine_name not like ''dt%'' and routine_name not like ''ms%'' and routine_name not like ''xp%'' '
  -- print @sqlsel
  --DECLARE @spro_cursor CURSOR    
  --  DECLARE spro_cursor CURSOR FOR SELECT  SPECIFIC_NAME, SPECIFIC_SCHEMA  from information_schema.routines where routine_type = 'PROCEDURE'
  EXEC (@sqlsel)
  --DECLARE spro_cursor CURSOR FOR @sqlsel
  OPEN spro_cursor
  FETCH NEXT FROM spro_cursor INTO @spname, @schemaname
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @sqlstmt = 'use [' + @name + ']' + CHAR(13) + CHAR(10) + '  GRANT VIEW DEFINITION ON [' + @schemaname + '].[' + @spname + '] TO [' + @userName + ']'
    PRINT @sqlstmt
    EXECUTE sp_executesql @sqlstmt
    FETCH NEXT FROM spro_cursor INTO @spname, @schemaname
  END
  CLOSE spro_cursor
  DEALLOCATE spro_cursor

  FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor