Microsoft KB Archive/271258

= BUG: Original db_datareader Role is Lost if dbuser is Assigned a New db_datawriter Role =

Article ID: 271258

Article Last Modified on 10/16/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q271258



BUG #: 55885 (SQLBUG_70)



SYMPTOMS
If the following conditions are true, then the dbuser role, db_datareader or db_datawriter, whichever was assigned first, is lost:
 * SQL Server is installed with Code page 936, 949 or 950.
 * The dbuser role db_datareader is assigned, and then the db_datawriter role is assigned or vice versa.

In SQL Server 7.0, with Code page 936, 949 or 950, if you create logins, and you then grant the database role db_datareader to a dbuser, and you then grant the db_datawriter role to the same dbuser, the original role, db_datareader, is lost. Similarly, if you assign the db_datawriter role first to a dbuser, and you then assign the db_datareader role to the same dbuser, the original role, db_datawriter, is lost.



WORKAROUND
IMPORTANT: This article contains information about editing the system table. Before you edit the system table, make sure that you have a good backup of the database and that you understand how to restore the database if a problem occurs.

You can use the following workaround to set both the db_datareader and db_datawriter roles to a dbuser when you are using SQL Server with Code page 936, 949 or 950: use pubs go sp_addlogin 'test' go sp_adduser 'test','test' go select * from sysusers WHERE NAME = 'test'

/*sp_helprolemember will not see the user 'test' in the 'db_datareader'*/

sp_helprolemember 'db_datareader' go

/*And sp_helprolemember won't see the user 'test' in the 'db_datawriter'*/

sp_helprolemember 'db_datawriter' go sp_configure 'allow', 1 go RECONFIGURE with override go

/* db_datareader role = 0x40, for db_datawriter role = 0x80, for both roles set= 0xC0 */

update sysusers set roles = 0xC0 where name = 'test' go sp_configure 'allow', 0 go RECONFIGURE with override go /*sp_helprolemember sees the user 'test' in the 'db_datareader'*/

sp_helprolemember 'db_datareader' go

/*sp_helprolemember sees the user 'test' in the 'db_datawriter'*/

sp_helprolemember 'db_datawriter'



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0.

Keywords: kbbug kbpending KB271258

-

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

© Microsoft Corporation. All rights reserved.