logins after db restoration

Question

logins after db restoration

sqlquery-101401

SSCarpal Tunnel

Points: 4951
More actions
April 28, 2004 at 5:14 am

#84993

after restoration of db ,login does not come bcz of server base so how to link logins with the users.
so that their rights can be maintained after restoration of db.
thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 1

If you restored into the same server, you shoudn't have any problems with the login, unless you have restored the master db.

sqlquery-101401 SSCarpal Tunnel Points: 4951 More actions · Answer 2

ya but that is a diff server,and if i create that login it says user already available ,if i delete the user then all the rights of that user also lost on different objects.

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 3

On the server that you are restoring to drop and recreate the logins with the sids matching the login sid on the first server, that way you won't run into this problem

select name, sid from master..syslogins

Shamless self promotion - read my blog http://sirsql.net

Sopheap Suy SSCertifiable Points: 7893 More actions · Answer 4

go

EXEC sp_change_users_login 'Update_One', 'Mary', 'NewMary'

This command above will fix your problem if you restore your database to different server, you still need to make sure the login exist on the new server before you could issue this command.

mom

Charlie-120533 SSCrazy Points: 2187 More actions · Answer 5

I sometimes use the following, provided the users are already a part of the Security\login:

(change the LoginName to the user who needs to be changed)

EXECUTE sp_change_users_login 'Auto_Fix', 'LoginName'

Gary Manino SSC Veteran Points: 227 More actions · Answer 6

Here's some code that will build the code to resynch the users after you restore. Just run it from the restored database.

select 'exec sp_change_users_login @action=''update_one'',@loginname=''' +

name + ''',@usernamepattern=''' + name + ''''

from sysusers

where uid > 2 and uid < 16000

hawg SSCertifiable Points: 6044 More actions · Answer 7

You might want to check out the following links from Microsoft about moving objects from one server to another. I have used this before (and I'm actually in the middle of a move right now) and it works really good.

HOW TO: Transfer Logins and Passwords Between Instances of SQL Server

http://support.microsoft.com/default.aspx?kbid=246133

HOW TO: Move Databases Between Computers That Are Running SQL Server

http://support.microsoft.com/default.aspx?kbid=314546

Good luck!

----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

Wang, KT SSC Enthusiast Points: 152 More actions · Answer 8

hawg,

I have a question? I try those scripts to transfer user login and password between 2 SQL 2000 servers. I can not get password for each user. There are blank at password column of primary server. I am sure each user have their owner password. Is those scripts just work at SQL server 7?

Thanks,

KT

hawg SSCertifiable Points: 6044 More actions · Answer 9

Are the logins that you are not getting passwords for Windows accounts. If so, these types of logins do not have passwords, only SQL logins?

I use this for moving from SQL 2K to SQL 2K.

I apologize for not completely understanding your question. Give me some more information if my reply does not answer your question.

hawg

----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

Wang, KT SSC Enthusiast Points: 152 More actions · Answer 10

hawg,

Sorry, I am talking about SQL 2K to SQL 2K. I just retry it again. It works now.

Thanks.

KT