Removal of the guest account may cause a 916 error in SQL Server 2000 SP4 or a handled exception access violation in earlier versions of SQL Server 2000

USE Accounts
GO
EXECUTE sp_grantdbaccess guest
				

   USE Accounts
   GO
   EXECUTE sp_revokedbaccess guest
				

--You can use this script to identify the missing guest user, report it, and then re-create the entry in the appropriate database.

/*NOTE: The following procedure is dependent on SQL Server system tables. The structure of these tables may change between versions of SQL Server, and selecting
directly from system tables is discouraged. */ 

/*Because this procedure modifies the system tables, the system administrator must enable updates to the system tables before creating this procedure. 
To enable updates, use this procedure:*/ 

USE master
GO
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO

/* ************** */ 
USE master
GO
IF OBJECT_ID ('sp_fix_missingguest') IS NOT NULL
    DROP PROCEDURE sp_fix_missingguest 
GO

CREATE PROCEDURE sp_fix_missingguest 
AS
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
-- Only a sysadmin can do this
IF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1)
  BEGIN
    RAISERROR(15003, 16, 1, N'sysadmin')
    RETURN 1 -- Failure
  END

DECLARE @db_name sysname
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @status int
DECLARE @guestrowcount int
DECLARE @updatecounter int

SET @updatecounter = 0
SELECT @guestrowcount = count(*) from model.dbo.sysusers where lower(name) = 'guest'
print ''
If (@guestrowcount = 0 )
  BEGIN
   print 'Guest was missing from model database. The account is now re-created'
   INSERT INTO model.dbo.sysusers(uid,status,name,sid,roles,createdate,updatedate,altuid,password)
   VALUES(2,0,'guest',0x00,0x00,getdate(),getdate(),0,NULL)
   set @updatecounter = @updatecounter + 1
  END

DECLARE XYZ CURSOR FOR   
select name from master.dbo.sysdatabases where status not in (status|32, status|64, status|128, status|256, status|512, status|1024, status|32768, status|1073741824)

OPEN XYZ  

FETCH NEXT FROM XYZ into @db_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
   --  A dynamic SQL statement to identify if the guest user row is deleted from any of the databases.
   SET @SQLString = N' select @guestrowcountOUT = count(*) from ' + @db_name +'.dbo.sysusers where lower(name) = ''guest'''
   SET @ParmDefinition = N'@guestrowcountOUT int OUTPUT'
   EXECUTE sp_executesql   @SQLString,   @ParmDefinition,   
			   @guestrowcountOUT=@guestrowcount OUTPUT

  If @guestrowcount = 0 and @db_name in ('master','tempdb')
  BEGIN
   print 'Guest was missing from ' + @db_name +'. The account is now recreated'
   EXEC ('INSERT into ' + @db_name +'..sysusers(uid,status,name,sid,roles,createdate,updatedate,altuid,password)   
   select uid,2,name,sid,roles,createdate,updatedate,altuid,password from model..sysusers where name=''guest''')
   SET @updatecounter = @updatecounter + 1
  END

  If @guestrowcount = 0 and @db_name not in ('model','master','tempdb')
  BEGIN
      print 'Guest was missing from ' + @db_name +'. The account is now recreated'
      EXEC ('INSERT into ' + @db_name +'..sysusers(uid,status,name,sid,roles,createdate,updatedate,altuid,password)   
   select uid,status,name,sid,roles,createdate,updatedate,altuid,password from model..sysusers where name=''guest''')
   SET @updatecounter = @updatecounter + 1
  END

-- If the guest user exists in master and tempdb, then the status must be equal to 2.
-- Because guest must always have access to master and tempdb.. see BOL
   SET @SQLString = N' select @statusOUT = status from ' + @db_name +'.dbo.sysusers where lower(name) = ''guest'''
   SET @ParmDefinition = N'@statusOUT int OUTPUT'
   EXECUTE sp_executesql   @SQLString,   @ParmDefinition,   
			   @statusOUT=@status OUTPUT

  If @status = 0 and @db_name in ('master','tempdb')
  BEGIN
  print @status
  print 'Guest user should always have accessing to ' + @db_name + 'database'
  EXEC (N'USE ' + @db_name +' UPDATE sysusers set status = (status & ~1) | 2, updatedate = getdate() where lower(name) = ''guest''')
  SET @updatecounter = @updatecounter + 1
  END

FETCH NEXT FROM XYZ into @db_name
END

CLOSE XYZ
DEALLOCATE XYZ

IF (@updatecounter = 0)
BEGIN
 PRINT 'No databases were updated.'
END
GO

/*After the procedure is created, immediately disable updates to the system tables: */ 
sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
GO

/* Step 2 -- Call the above stored procedure */ 
EXEC master.dbo.sp_fix_missingguest