I'm currently in ASE 15.0.2 ESD #6.
For many years, when "cloning" a database to a new machine I have used bcp
to copy over the syslogins, syssrvroles, and sysloginroles tables. (The idea
is that, after doing that and then moving over my user database via
dump/load, I end up with an exact copy of the entire environment on the new
machine, complete with usernames and passwords.) It has always been my
understanding that this is a standard way to accomplish this.
When using bcp I specify the -b1 option. This way, any rows that are already
in those tables in the empty database (like for sa) will error out and get
skipped, and I'll import just the "new" rows.
This seems to work fine for syslogins and syssrvroles. But I just noticed
that sysloginroles allows duplicate rows. The effect is that the rows for
suid 1 will get duplicated in my target database. I checked some of the
databases that I have cloned in this way over the past few months, and they
all have duplicated rows in sysloginroles for sa! Some of them have three or
more sets of duplicate rows (I guess because I repeated the bcp operation
more than once).
My first question is, why isn't there a uniqueness constraint on
sysloginroles, like there is on most all similar system tables?
My second question is, do I have a problem? I assume not, since I've been
doing it this way for years (across many ASE versions) and apparently have
had duplicate rows without knowing it, and to my knowledge it's caused no
problem. But is there any potential problem that I should try to solve?
One funny symptom is that when I use "sp_displayroles sa" in the cloned
database I can see the duplicates. Like this:
I never noticed that before, until today.
Subject: sysloginroles allows duplicate rows?
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
Date: 10 Feb 2010 10:35:17 -0800
X-Trace: forums-1-dub 1265826917 10.22.241.152 (10 Feb 2010 10:35:17 -0800)
X-Original-Trace: 10 Feb 2010 10:35:17 -0800, vip152.sybase.com
Xref: forums-1-dub sybase.public.ase.general:28963
Article PK: 78203
Subject: Re: sysloginroles allows duplicate rows?
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
Date: 11 Feb 2010 07:52:03 -0800
X-Trace: forums-1-dub 1265903523 10.22.241.152 (11 Feb 2010 07:52:03 -0800)
X-Original-Trace: 11 Feb 2010 07:52:03 -0800, vip152.sybase.com
Xref: forums-1-dub sybase.public.ase.general:28964
Article PK: 78204
that is interesting. You might want to open a case with TS and ask to
attach your case to CR344729 which asks that a unique constraint be placed
on (suid,srid) for that table. Duplicate rows in that table might also
prevent an upgrade from 12.x to 15.x to occur successfully.