To prevent the behavior, do not manually delete the system
supplied user accounts from any of the SQL Server databases. Microsoft does not
support the removal of system supplied users including guest and dbo.

To work around the problem, query the sysusers system table in all the databases, including the system databases
and make sure that the guest and all other system users exist in each of the
databases.

Please note that the sp_helpuser stored procedure may not display all the system supplied users.
Therefore, you must directly query the sysusers system table.

The guest user account must always be allowed access to the master and tempdb databases. By default the guest user is denied access to the model database. Therefore, the guest user does not have access to any of the new databases that you
create.

If you want to allow the guest account access in any of the databases, use the sp_grantdbaccess stored procedure to grant access.

For example, to allow
a guest user account access to a database named Accounts, run the following code in the SQL Server Query Analyzer:

USE Accounts
GO
EXECUTE sp_grantdbaccess guest

Similarly, if you want to remove the guest user from any user database, use the sp_revokedbaccess stored procedure:

USE Accounts
GO
EXECUTE sp_revokedbaccess guest

Do not perform a direct DELETE statement executed from the sysusers system table (this is not supported).

After you execute
sp_revokedbaccess or sp_dropuser for the guest account, it remains in the sysusers table but you cannot use it. At this point, the column
"HasDBAccess" has been marked to 0/false for the guest ID. Because it is a
special ID, it is not actually removed from the table, but no access is
permitted.

If you do sp_helpuser when HasDBAccess = 0/false, the guest row is not displayed. If
you sp_helpuser when HasDBAccess = 1/true, it is not displayed. When it is not
visible in sp_helpuser, no one can use it to gain access.

If you execute sp_helpuser when HasDBAccess = 0/false, the guest row is not displayed. If
you execute sp_helpuser when HasDBAccess = 1/true, the guest account is displayed. When
it is not visible in sp_helpuser, no one can use it to gain access. If HasDBAccess = 0/false for
guest, stored procedures such as sp_dropuser and sp_revokedbaccess treat it as if it does not exist. If
you execute sp_revokedbaccess 'guest' when HasDBAccess = 0/false for guest, the
stored procedure returns the following message:

Server:
Msg 15008, Level 16, State 1, Procedure sp_revokedbaccess, Line 36 User
'guest' does not exist in the current database.

If an
unsupported, direct delete of the guest user has been executed on the sysusers table of one of the user databases, you can fix the problem by
using the following stored procedure and re-create the guest row:

--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

If the AV occurs, SQL Server may generate a stack dump and
write out information to the SQL Server error log file and a symptom dump file
is generated. The beginning of the stack dump in the SQL Server error log file
may appear similar to the following examples.