Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

sysloginroles allows duplicate rows?

2 posts in General Discussion Last posting was on 2010-02-11 15:52:03.0Z
John Flynn Posted on 2010-02-10 18:35:17.0Z
From: "John Flynn" <jflynn@miqs.com>
Newsgroups: sybase.public.ase.general
Subject: sysloginroles allows duplicate rows?
Lines: 57
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b72fc65$1@forums-1-dub>
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
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28963
Article PK: 78203

Hi.

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:

Role Name
------------------------------
sa_role
sa_role
sa_role
sso_role
sso_role
sso_role
oper_role
oper_role
oper_role
sybase_ts_role
sybase_ts_role
sybase_ts_role
replication_role
replication_role

I never noticed that before, until today.

Thanks.
- John.


Sherlock, Kevin [TeamSybase] Posted on 2010-02-11 15:52:03.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4b72fc65$1@forums-1-dub>
Subject: Re: sysloginroles allows duplicate rows?
Lines: 66
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b7427a3$1@forums-1-dub>
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
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
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.

"John Flynn" <jflynn@miqs.com> wrote in message
news:4b72fc65$1@forums-1-dub...
> Hi.
>
> 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:
>
> Role Name
> ------------------------------
> sa_role
> sa_role
> sa_role
> sso_role
> sso_role
> sso_role
> oper_role
> oper_role
> oper_role
> sybase_ts_role
> sybase_ts_role
> sybase_ts_role
> replication_role
> replication_role
>
> I never noticed that before, until today.
>
> Thanks.
> - John.
>
>