April 16, 2002 at 2:26 pm
I have to move a production database onto a new server. I found a script from Microsoft that transfers logins:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q246133
What I am looking for is a script or an idea about how to add all 150 users to the appropriate db with the appropriate associated roles. I would prefer not to do this manually. Thanks in advance for any ideas.
April 16, 2002 at 2:34 pm
The only problem is that I am spanning my database across two drives and I am changing files and filegroup ect( I figure now is a good time to move the hotly contested tables to their own drive), so I don't think this will work.
April 16, 2002 at 6:00 pm
You're trying to move logins or database users? Logins are just data in sysxlogins, you can BCP, DTS, or in SQL2K there is even a special DTS task. Users get moved with the database. NT users will be fine as long as they have a corresponding login, SQL logins if added manually will have a different sid and you will have to fix using sp_change_users_login.
Andy
April 17, 2002 at 8:40 am
As I understand it sp_change_users_login is manual, so it would not save me much time over going into each user and granting them access to the db. Sounds like I may have to suck it up.
April 17, 2002 at 9:40 am
Only if the Sql logins already exist on BOTH servers with different SID's. If you bcp the info over (or any other method) that preserves the SID, then everything works, period.
Andy
April 17, 2002 at 10:12 am
I have developed a VB application using SQLDMO to transfer tables, logins, users, permissions etc.. If you need any help, contact me. I might be able to help you. (My application is heavily customised to do what we want here. But it can pretty much go against any database and transfer logins and users etc..).
Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.
April 17, 2002 at 1:34 pm
Isn't there an issue with bcping passwords? Which master tables should I bring over? What if the databases on the new server will not match those on the old, won't that cause a problem?
April 17, 2002 at 2:35 pm
As I mentioned earlier, the passwords are in sysxlogins. It's encrypted, but data just the same. Sp_addlogin provides a good example of how to move the data using TSQL. The key is you don't have to decrypt the password to move it. Logins are in the master, so the only way they impact databases is if you have a collision (same login on both servers with different password) or if the login exists on both servers with same password but DIFFERENT sid. That's why you have to use sp_change_users_login, but again, if you look at the code in the proc there is no reason you can't do a single update, or as an alternate plan, just use a cursor to process all the logins in sysusers in the db. This only matters for SQL logins, NT logins always have the same SID.
Andy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply