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

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'

BUG: Original db_datareader Role is Lost if dbuser is Assigned a New db_datawriter Role (271258)

SYMPTOMS

WORKAROUND

STATUS