if exists (select * from sysobjects where id = object_id('dbo.sp_send_user_msg') and sysstat & 0xf = 4)
  begin
    drop procedure dbo.sp_send_user_msg
    print 'dropping old sp_send_user_msg procedure'
  end
GO

print 'Creating PROCEDURE sp_send_user_msg'
go

CREATE PROCEDURE sp_send_user_msg 
       (
         @delay    smallint = 5
       , @msgin    varchar(255) = null
       )
AS
  begin
/*----------------------------------------------------------------------------*/
/* Set the default delay if none received                                     */
/*----------------------------------------------------------------------------*/
if @delay = null
  select @delay = 5


/*----------------------------------------------------------------------------*/
/* Declare local variables                                                    */
/*----------------------------------------------------------------------------*/
DECLARE @suid 		smallint
DECLARE @name 		varchar(30)
DECLARE @msg 		varchar(255)
DECLARE @msg1 		varchar(255)
DECLARE @msg2 		varchar(255)
DECLARE @msg3 		varchar(255)
DECLARE @msg4 		varchar(255)
DECLARE @net_address    varchar(12)

/*----------------------------------------------------------------------------*/
/*  Define and build the message to send to the user(s)                       */
/*----------------------------------------------------------------------------*/
SELECT @msg1 = '"NET SEND '

if @msgin is null
  begin
    SELECT @msg2 = "ATTENTION: SQL Server will be brought down for maintenance in "                 
    SELECT @msg3 = convert(varchar(10), @delay) + ' mins !!   Please LOGOFF....."'
  end
else
  begin
    select @msg2 = @msgin
    select @msg3 = ''
  end

SELECT @msg4 = '"'

/*----------------------------------------------------------------------------*/
/*  Declare and Open the cursor for use                                       */
/*----------------------------------------------------------------------------*/
DECLARE hUser CURSOR FOR SELECT DISTINCT suid, hostname, net_address FROM master..sysprocesses WHERE suid > 2  and suid < 16000
OPEN hUser

/*----------------------------------------------------------------------------*/
/*  Do the initial fetch                                                      */
/*----------------------------------------------------------------------------*/
FETCH hUser INTO @suid, @name, @net_address

/*----------------------------------------------------------------------------*/
/* For each record retrieved by the fetch                                     */
/*----------------------------------------------------------------------------*/
WHILE (@@fetch_status = 0)
  BEGIN

    /*-------------------------------------------------------------------------*/
    /* if user does not have a machine name, get their ID from                 */
    /* the syslogins table, and use that for the message designator            */
    /*-------------------------------------------------------------------------*/
    if @name is null or @name = ''
      begin 
        SELECT @name = name
        FROM master..syslogins
        WHERE suid = @suid
      end

    /*-------------------------------------------------------------------------*/
    /* Send each connected user a message                                      */
    /*-------------------------------------------------------------------------*/
    begin
      SELECT @msg = @msg1 + @name + @msg2 + @msg3 + @msg4
      EXEC master..xp_cmdshell @msg, no_output        
    end 

    /*-------------------------------------------------------------------------*/
    /*  Do the next fetch (within the loop)                                    */
    /*-------------------------------------------------------------------------*/
    FETCH hUser into @suid, @name, @net_address

  end 

/*----------------------------------------------------------------------------*/
/* Close and de-allocate the cursor                                           */
/*----------------------------------------------------------------------------*/
CLOSE hUser
DEALLOCATE hUser

RETURN(0)
end
go


begin
  GRANT  EXECUTE  ON dbo.sp_send_user_msg  TO public
  print 'granting execute on sp_send_user_msg to PUBLIC'
end
GO

print 'Create procedure sp_send_user_msg complete'
go