December 14, 2008 at 8:20 am
Hi
I'm going to restore a database(A) by taking backup of database(B) from different server.Before restoring i would like to create a script of users in the database(A).Can anyone help me for this.I'm using SQLSERVER 2000.
Thanks in advance.:cool:
December 14, 2008 at 8:45 am
There is no need to create such a script. The users will be restored in the new server with the database, because they are stored in the database. You'll might have some problems with users that point to wrong logins (if you use SQL Server authentication). In this case you can fix it with sp_change_users_login procedure (which you can read about in BOL).
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 14, 2008 at 8:48 am
Read this article on synchronizing logins http://www.sqlmag.com/Article/ArticleID/25710/Synchronizing_Logins.html
December 14, 2008 at 8:56 am
The user from database(A) differs from users of database(B).so when i restored the database i need to execute the sciript so that the users must exists as previous.And also if the restoration fails or need any backout i need to have the same users as pervious.
December 14, 2008 at 9:02 am
Create logins based upon the SID of one machine and create them on the other. When you restore the databases because the SID matches the one in the database it will have the same rights and permissions as on the old
December 14, 2008 at 9:20 am
If both databases have different users. db A contains users user1, user2, user3 and db B contains users user1, user2, user4 and you want to add user4 to db A after restoring, the above mentioned article will help you.
It just came to my mind that u might be confused with Logins and Users. Logins are created at the server level, so all logins created on the server containing db A will not have any effect of ur restoration of db B.
Seems you have two different servers where server1 contains db A and server2 contains db B and you're trying to restore db B on server1. if this is the case, all users associated in db B will automatically be restored. If u face issues with some users, you can refer to what Adi has written.
December 14, 2008 at 12:28 pm
if for some reason you dont bring the logins across all is not lost
example.
database A has a user called BOB and there is a login called BOB on SQL server A.
You restore database A to SQL server B only
to fix this create a login on SQL server B called BOB then run
exec sp_change_users_login 'Update_One','BOB','BOB'
hey presto SID mismatch is fixed
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 15, 2008 at 3:07 am
To transfer logins and passwords between two instances of SQL Server 2000, you can use the DTS Package Transfer Logins Task in SQL Server 2000. For details, have a look here:
http://support.microsoft.com/kb/246133
James Howard
December 15, 2008 at 7:00 am
to transfer users from one system to another I typically use sp_help_revlogin. There's a different sp for sql200 and sql2005 so make sure you download the proper one (Even a differnt version yet to transfer logins from sql2000 to sql2005). You can google and get it from several places. Also, the sql2000 version does not transfer the internal SID, the sql2005 version does.
Tim White
December 15, 2008 at 7:10 am
sp_help_revlogin sp's attached....
Tim White
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply