Hello,

We have a requirement to place the databases & users from 2 seperate Sybase 11.9.2 servers and rebuild them onto a single entity also on 11.9.2.

I have used the following procedure to bcp out the syslogins from 1 server onto the new machine but I need to know how i can then import the 2nd set of syslogins bearing in mind that a large percentage of them will be the same user.

Steps for migrating logins from OLDSERVERNAME to NEWSERVERNAME

1. bcp out syslogins from OLDSERVERNAME using
bcp master..syslogins out OLDSERVERNAME_logins.txt –Usa –Pxxxx -SOLDSERVERNAME–c –t,

2. Manually delete the first two rows from OLDSERVERNAME_logins.txt file.
[ Make sure it is ‘sa’ and ‘mon_user’ with suid 1 and 3 ]

3. Delete syslogins entry by using
sp_configure ‘allow updates’,1
go
Delete from master..syslogins where suid > 3
Go

4. bcp master..syslogins in OLDSERVERNAME_logins.txt –Usa –Pxxxx -SNEWSERVERNAME -c –t,

5. Run the below script on each databases which are refreshed from OLDSERVER.

EXEC sp_configure "allow updates", 1
go

/* delete names not found in master syslogins */
DELETE FROM sysusers
WHERE suid > 1
and uid < =16383
and uid > 1
and name not in
(SELECT name FROM master..syslogins)
go

/* update sysusers and resync all uids from the existing suids */
IF exists(SELECT 1
FROM sysobjects
WHERE name = "sysusers_holding"
and type = "U")
DROP TABLE sysusers_holding
go

CREATE TABLE sysusers_holding (
id NUMERIC(6,0) IDENTITY PRIMARY KEY,
suid INT null,
uid INT null,
gid INT null,
name CHAR(30) null,
environ VARCHAR(255) null)
go

/* populate the holding table */
INSERT INTO sysusers_holding (suid, gid, name, environ)
SELECT suid, gid, name, environ
FROM sysusers
WHERE suid > 1
and uid < = 16383
and uid > 1
go

/* update the uids. */
DECLARE syncuser CURSOR FOR
SELECT name, uid
FROM sysusers_holding
FOR UPDATE OF uid, suid
go

DECLARE @name CHAR(30), @uid INT, @suid INT
SELECT @suid = 9999
OPEN syncuser
FETCH syncuser INTO @name, @uid
WHILE (@@sqlstatus != 2)
BEGIN
IF exists(SELECT 1
FROM master..syslogins
WHERE name = @name)
/* got the name - update the uid */
BEGIN
SELECT @suid = suid
FROM master..syslogins
WHERE name = @name
UPDATE sysusers_holding SET uid = @suid, suid = @suid WHERE CURRENT OF syncuser

IF @@ERROR != 0
BEGIN
ROLLBACK TRANSACTION
BREAK
END

SELECT @suid = 9999
END
FETCH syncuser INTO @name, @uid
END
CLOSE syncuser
go

DEALLOCATE CURSOR syncuser
go

/* now move records over */
DELETE FROM sysusers
WHERE suid > 1
and uid < =16383
and uid > 1
go

INSERT INTO sysusers
SELECT suid, uid, gid, name, environ
FROM sysusers_holding
go

/* sp_helpuser will show any mis-matches) */
EXEC sp_helpuser
go

EXEC sp_configure "allow updates", 0
go


any help greatly appreciated both for the current process I'm using and getting the 2nd set of users imported

Regards

A few questions:
- Are there logins that exist in both old servers?
- Will userdb's from both old servers be migrated to the new server?

And the most important one: Is it neccesary to copy existing passwords from both servers to the new server?
If not, you can just generate the create-loginscripts and create-userscripts on the old servers and run them on the new server. That way the logins and users wille created properly, with minimal risk of something happening during the manipulation of systemtables.
You can set the paswords to just something, and the users can change it to something else.

Hi,

- yes, there are logins that exist on both old servers
- yes, all databases from both servers need to be migrated to the new one
- ideally yes, if there is a process to enable the users to continue using their current passwards then the business sponser would choose that. however if there is an increased risk of the process not working then we could make an arguement for users to have to change their passwords after the migration

First of all, it's an interesting case you have . Bit of a challenge, but fun.

Although your posted procedure looks like it will work, there's a lot of manipulating systemtables, and that's something you want to avoid as much as possible.
I assume you copy the databases with a dump/load-command?

How about the following process:
1: bcp-out the logins from the old servers (use flag -F<number> to specify the row at which to start, to skip sa & monuser)
2: recreate all logins needed from oldserver1. do not recreate the users, as they're already present in the loaded databases.
3: userdb..sysusers is probeblay mismatched. Using 'allow update', update sysusers.suid with the corresponding suid from master..syslogins, joinging both table on column 'name' (assuming loginname and username are identical)
At this point you should have the proper logins with matching users in the databases from oldserver1. All privileges granted to the users should be kept in place. Check that if needed/wanted.

4: bcp in the login-export from oldserver1 in a temporary table in newserver1 (either in masterdb or tempdb, doesnt matter too much).
5: using 'allow update', update master..sysusers, column password, with the proper value from the temptable. join the tables on name, not on suid. The suid is not necessarily the same, since you recreated the logins.
The logins should have the password from oldserver1 by now.

Repeat the steps above for oldserver2 and it's databases. You probably will get errors creating logins that are already present from oldserver1. That is no problem, they wont be overwritten.
Be advised that when you get to step 5, you will overwrite the passwords of the logins that are present in both oldserver1 and oldserver2. The logins will end up with the password of oldserver2. Not really a problem, just something for the user to know.

Disclaimer: I haven't tested this myself, so be a little bit careful .