Microsoft KB Archive/75544

= PRB: Reasons for Receiving Message Numbers 3115 and 3116 =

Article ID: 75544

Article Last Modified on 2/14/2005

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition

-



This article was previously published under Q75544



SYMPTOMS
Upon loading a database from a dump made on another server (Case 1) or upon loading a dump after re-creating the database (Case 2), the following messages are received:

  User X in database Y has suid Z1 which is the same as   the suid of the dbo ( as defined in sysdatabases ). User X  will be given suid Z2       ( Msg. 3115, Level 0, State 1 )

This message is followed by:

  When all load transactions have been completed for database Y, user X should be given a reasonable suid ( Msg. 3116, Level 0, State 1 )



Case 1
When the dump is loaded on a different 4.2x server, these messages occur because the suid of the database owner (DBO) (as indicated in the sysdatabases table in the master database) does not match the suid of the DBO in the database loaded from the dump (as indicated in the sysusers table). Furthermore, the suid indicated for the owner in the sysdatabases table is associated with a user in the sysusers table in the loaded database. Starting with version 6.x, the DBO is automatically changed to SA if there is no corresponding login id for the DBO of the loaded database.

Case 2
When the database is recreated and loaded back from a dump, the above messages be received only if the system administrator (SA) has inadvertently been added as a user in sysusers. This creates a conflict similar to Case 1 because the SA cannot exist both as a DBO and as a user in sysusers. SA cannot be added as a user beginning with version 7.0.

Therefore, instead of arbitrarily deleting the row, SQL Server 4.2x changes the conflicting suid in sysusers to a negative number (usually -99), thus providing the opportunity to re-establish the original owner by changing the value of suid as suggested by Msg. 3116 above.

Please note that in Case 2, if the SA has not been added as a user in sysusers, SQL Server does NOT display any of the above messages; however, the row from sysusers is still deleted for the previous owner. As a result, the previous owner will not be able to access the database at all until restored as owner or at least as a user.



WORKAROUND
As indicated by the level of the messages (level 0), there are no ill effects on the database. The messages are merely informing the user of the housekeeping that SQL Server is doing to successfully restore the database.

The ownership can subsequently be changed back to the original owner by using the stored procedure sp_changedbowner, using SQL Administrator, or by removing the row with the negative suid (usually -99) from the sysusers table.

Keywords: kbother kbprb KB75544

-

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

© Microsoft Corporation. All rights reserved.