SYMPTOMS
If you expand a database folder in Microsoft SQL Server Enterprise
Manager (SEM), a handled exception access violation (AV) occurs if all the
following conditions are true:
- The guest user account has been deleted from the sysusers system table in one of the databases with a DELETE statement
issued directly against the system table.
- SQL Server is registered in SEM with a user account
that does not have access to the database from which the guest account was
manually removed.
- The account that you used to register SQL Server is not a
member of the sysadmin fixed server role.
Note If you are running SQL Server 2000 SP4, and the
guest user account has been deleted from the
sysusers system table, you will receive the following error message:
Error 916 - Server user 'DelSysusers_003_Login' is not a valid user in database 'db_DelSysusers_003'
Also, when you run the DBCC CHECKCATALOG statement, you will receive the following message: Server: Msg 2510, Level 16, State 1, Line 1 System table corrupt: Missing row in sysusers for 'guest' is required for SQL Server operation. For more information, see Microsoft Knowledge Base article 315523. After you expand the
Databases folder, SEM may not display any databases and you will
just see
(No items).
Note This behavior is not limited to expanding a database folder in SEM. The behavior can also occur if an application tries to use a database that has had the
guest account removed.
WORKAROUND
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