Change in Database User Perms when copying Database

[Mahmood Sayed]

How to maintain Database User Permissions when copying the Database from One SQL Server to another(Either through backups or sp_detach). The reason is the login sid is different in the target server and as a result the database user is not able to map to the login existing in the target server. The only way I can correct this is through dropping and recreating the user's again and assign the permissions, or change the system catalog - sysusers to remap the login to the user in the database.
I do not wish to use the sp_addalias as it is available only for backward compatibility.
Is there a better way of doing this ?

[Stan]

Yes. That is, if you want the SAME permissions to exist on the 2nd server.

Enable updates to your system tables (sp_configure 'allow updates', 1).
Recreate the server LOGINS specifying the sid from the first server (you can script this and add the sid and passwords).
Remember to set allow updates back to 0.


------------
Mahmood Sayed at 3/9/00 5:23:37 PM

How to maintain Database User Permissions when copying the Database from One SQL Server to another(Either through backups or sp_detach). The reason is the login sid is different in the target server and as a result the database user is not able to map to the login existing in the target server. The only way I can correct this is through dropping and recreating the user's again and assign the permissions, or change the system catalog - sysusers to remap the login to the user in the database.
I do not wish to use the sp_addalias as it is available only for backward compatibility.
Is there a better way of doing this ?

if you set 'allow updates' to 1 you should be in single user mode.

------------
Stan at 3/9/00 5:32:00 PM

Yes. That is, if you want the SAME permissions to exist on the 2nd server.

Enable updates to your system tables (sp_configure 'allow updates', 1).
Recreate the server LOGINS specifying the sid from the first server (you can script this and add the sid and passwords).
Remember to set allow updates back to 0.


------------
Mahmood Sayed at 3/9/00 5:23:37 PM

How to maintain Database User Permissions when copying the Database from One SQL Server to another(Either through backups or sp_detach). The reason is the login sid is different in the target server and as a result the database user is not able to map to the login existing in the target server. The only way I can correct this is through dropping and recreating the user's again and assign the permissions, or change the system catalog - sysusers to remap the login to the user in the database.
I do not wish to use the sp_addalias as it is available only for backward compatibility.
Is there a better way of doing this ?

[Stan]

When allow updates is enabled (set to 1), any user who has appropriate permissions can update system tables directly with ad hoc updates and can create stored procedures that update system tables.


------------
at 3/9/00 5:40:17 PM


if you set 'allow updates' to 1 you should be in single user mode.

------------
Stan at 3/9/00 5:32:00 PM

Yes. That is, if you want the SAME permissions to exist on the 2nd server.

Enable updates to your system tables (sp_configure 'allow updates', 1).
Recreate the server LOGINS specifying the sid from the first server (you can script this and add the sid and passwords).
Remember to set allow updates back to 0.


------------
Mahmood Sayed at 3/9/00 5:23:37 PM

How to maintain Database User Permissions when copying the Database from One SQL Server to another(Either through backups or sp_detach). The reason is the login sid is different in the target server and as a result the database user is not able to map to the login existing in the target server. The only way I can correct this is through dropping and recreating the user's again and assign the permissions, or change the system catalog - sysusers to remap the login to the user in the database.
I do not wish to use the sp_addalias as it is available only for backward compatibility.
Is there a better way of doing this ?

[Mahmood Sayed]

Guys - I have mentioned in my query that that is exactly what I end up doing - ie. making changes to the system catalog (although I dont prefer changing the sysxlogins table but I do change the sysusers table of the concerned database to reflect the sid of the user in the sysxlogins system table.

But this I always thought should be avoided. Is there any other way out or this is it ?


------------
Stan at 3/9/00 5:44:05 PM

When allow updates is enabled (set to 1), any user who has appropriate permissions can update system tables directly with ad hoc updates and can create stored procedures that update system tables.


------------
at 3/9/00 5:40:17 PM


if you set 'allow updates' to 1 you should be in single user mode.

------------
Stan at 3/9/00 5:32:00 PM

Yes. That is, if you want the SAME permissions to exist on the 2nd server.

Enable updates to your system tables (sp_configure 'allow updates', 1).
Recreate the server LOGINS specifying the sid from the first server (you can script this and add the sid and passwords).
Remember to set allow updates back to 0.


------------
Mahmood Sayed at 3/9/00 5:23:37 PM

How to maintain Database User Permissions when copying the Database from One SQL Server to another(Either through backups or sp_detach). The reason is the login sid is different in the target server and as a result the database user is not able to map to the login existing in the target server. The only way I can correct this is through dropping and recreating the user's again and assign the permissions, or change the system catalog - sysusers to remap the login to the user in the database.
I do not wish to use the sp_addalias as it is available only for backward compatibility.
Is there a better way of doing this ?