Hi,

Doing a Database Refresh activity.

Taken backup of PROD server wanted it to restored on UAT server.

Before restoring it on UAT server taken backup of sysusers and sysalternates to retain the user info on the UAT server.

Loaded UAT and done bcp in of sysusers and sysalternates.

But now enduser is complaining about permission issue.

Checked and found that there is Group ID mismatch.

Gid from PROD and UAT have been different.

How do I deal these situation. Has anybody faced such a scenario.

Please suggest.

Generate a script with the group memberships, and all the permissions assigned to these groups. Then drop the groups, and re-add them in the same order they appear in the production system. Confirm the groups have the same GIDs. This should only have to be done once, as the bcp out of the sysusers table should cover any future refreshes.

Thanks Mcrowley .... But I find generating the script for group permission is tedious (if tools are not used)

How about updating the gid directly in the sysusers .... is it ok ... has anybody tried this out ...

Updating the systables is possible, but be careful. Only do this kind of stuff if you're sure what you're doing.
My experience is limited to Adaptive Server Enterprise, and in ASE groups are special entries in sysusers.
What is the exact problem? Are users not part of the group with permissions? Or does the group not have the proper permissions in the database?

use <dbname>
go
sp_configure 'allow updates', 1
go
update sysusers set a.suid = b.suid from sysusers a, master..syslogins b
where a.name = b.name
go
sp_configure 'allow updates', 0
go

the above should reset the suid in the sysusers table in the database

suda

From my practice, I would suggest to put "begin tran" before the update statement :-) And commit it only after checking that sysusers contains what is expected...

For the first time, do it manually, record the actions in the script for particular database and next time refreshing UAT you can run this "fix" from script.