Microsoft KB Archive/315523

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

Article ID: 315523

Article Last Modified on 4/27/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q315523



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.



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



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 &quot;HasDBAccess&quot; 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



MORE INFORMATION
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.

SQL Server 2000 Service Pack 2
 --- ---
 * BEGIN STACK DUMP:
 * 01/25/02 14:00:17 spid 51
 * Exception Address = 00401EA0 (RecBase::Resize + 00000005 Line 0+00000000)
 * Exception Code   = c0000005 EXCEPTION_ACCESS_VIOLATION
 * Access Violation occurred reading address 00000000
 * Input Buffer 68 bytes -
 * exec sp_MSdbuseraccess N'db', N'%'
 * Short Stack Dump
 * 00401EA0 Module(sqlservr+00001EA0) (RecBase::Resize+00000005)
 * 00441CFD Module(sqlservr+00041CFD) (CSysScan::GetVaried+0000002A)
 * 005ACC39 Module(sqlservr+001ACC39) (CUserScan::CbGroupBitmap+00000016)
 * 00622033 Module(sqlservr+00222033) (SecCache::FGetFromDiskScedb+00000317)
 * 0043FF36 Module(sqlservr+0003FF36) (checkdbperm+000000EF)
 * 004400FF Module(sqlservr+000400FF) (usedb+0000009F)
 * 00624ABE Module(sqlservr+00224ABE) (FHasDbaccess+00000076)
 * 005B99A4 Module(sqlservr+001B99A4) (I4HasDbaccessWstr+00000011)
 * 00419CE3 Module(sqlservr+00019CE3) (CEs::GeneralEval4+00000059)
 * 0042F36B Module(sqlservr+0002F36B) (CStmtAssign::XretExecute+00000102)
 * 0040F403 Module(sqlservr+0000F403) (CMsqlExecContext::ExecuteStmts+000002D9)
 * 0040EA95 Module(sqlservr+0000EA95) (CMsqlExecContext::Execute+000001B6)
 * 00410159 Module(sqlservr+00010159) (CSQLSource::Execute+00000331)
 * 0046C55A Module(sqlservr+0006C55A) (CStmtExec::XretLocalExec+0000014D)
 * 0046C3F6 Module(sqlservr+0006C3F6) (CStmtExec::XretExecute+0000031A)
 * 0040F403 Module(sqlservr+0000F403) (CMsqlExecContext::ExecuteStmts+000002D9)
 * 0040EA95 Module(sqlservr+0000EA95) (CMsqlExecContext::Execute+000001B6)
 * 00410159 Module(sqlservr+00010159) (CSQLSource::Execute+00000331)
 * 0053C498 Module(sqlservr+0013C498) (language_exec+000003E1)
 * 00411099 Module(sqlservr+00011099) (process_commands+000000EC)
 * 41073379 Module(UMS+00003379) (ProcessWorkRequests+0000024A)
 * 41073071 Module(UMS+00003071) (ThreadStartRoutine+000000BD)
 * 7800A27B Module(MSVCRT+0000A27B) (beginthread+000000CE)
 * 77E8758A Module(KERNEL32+0000758A) (SetFilePointer+0000018A)

Microsoft SQL Server 7.0 RTM
Short Stack Dump 0x00402624 Module(sqlservr+2624) (RecBase::Resize+3) 0x00405897 Module(sqlservr+5897) (RecBase::LocateVarColumn+10) 0x004115db Module(sqlservr+115db) (CSysScan::GetVaried+34) 0x005bf61d Module(sqlservr+1bf61d) (CUserScan::CbGroupBitmap+16) 0x00519c89 Module(sqlservr+119c89) (SecCache::FGetFromDiskScedb+2a6) 0x004042df Module(sqlservr+42df) (checkdbperm+dd) 0x00446d6b Module(sqlservr+46d6b) (login+2e8) 0x4106187f Module(opends60+187f) (execute_event+659) 0x410645a5 Module(opends60+45a5) (process_login+78) 0x41061b43 Module(opends60+1b43) (process_commands+d6) 0x41068eff Module(opends60+8eff) (process_loginread+14c) 0x41092be1 Module(ums+2be1) (ProcessWorkRequests+ed) 0x4109336a Module(ums+336a) (ThreadStartRoutine+139) 0x77c37e53 Module(MSVCRT+27e53) (endthread+aa) 0x77e802ed Module(kernel32+202ed) (OpenConsoleW+b8) 0x00000000 Module(sqlservr+ffc00000) ---

Additional query words: access violation AV exception

Keywords: kbprb KB315523

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.