| Author |
Topic |
|
sektor
Starting Member
14 Posts |
 Posted - 2006-09-27 : 13:06:37
|
Ok. So we just moved over one DB from our original server to another server. The DB copied over fine.Our next step is trying to see if there is a way to move accounts. Is that possible?We have about 40 accounts (using SQL authentication) on the original server. Is there a way to move or copy those accounts to the new SQL server that is holding that same DB?Haven't found anything in the Enterprise manager, so im thinking it will be something to do in the SQL analyzer.My lack of knowledge in SQL is not helping me much here.  I appreciate the help. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-27 : 15:44:05
|
| http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=16090&DisplayTab=ArticleTara Kizer |
 |
|
|
SreenivasBora
Posting Yak Master
164 Posts |
Posted - 2006-09-27 : 16:33:19
|
| 1. Go to enterprise manager and extract the Login user script and execute on other serverOR2. BCP out the logins info and BCP in on other server.With RegardsBSR |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-27 : 16:35:47
|
| I wouldn't recommend either option. The link that I posted allows you to copy the passwords. The options that BSR posted do not give you this option. Plus option 2 means you'd have to modify system tables which is highly not recommended.Tara Kizer |
|
|
|
sektor
Starting Member
14 Posts |
Posted - 2006-09-28 : 12:42:57
|
| So with that provided link (Thank you BTW tkizer), what is the proper way to run that? I am very new to this and still getting my head wrapped around it.Would I execute that script in the link above in the SQL analyzer on the new SQL box? If the old SQL box is PRDDB and the new SQL box is TESTDB, what would be the correct procedure to do this? I'm a little hesitante because im worried something will might go wrong on my production DB box (PRDDB).I appreciate it. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-28 : 12:45:33
|
| You run the script on the old one, copy the output to the new one then run it on the new one. That should be explained in the article.Tara Kizer |
|
|
|
sektor
Starting Member
14 Posts |
Posted - 2006-09-28 : 13:01:06
|
quote:
Originally posted by tkizer
You run the script on the old one, copy the output to the new one then run it on the new one. That should be explained in the article.Tara Kizer
Thanks. Being extra careful because I am not completely familiar with SQL (although im learning). So just take this script and run it in SQL analyzer?quote:
SET NOCOUNT ONSELECT 'EXEC sp_addlogin @loginame = ''' + loginname + '''',', @defdb = ''' + dbname + '''',', @deflanguage = ''' + language + '''',', @encryptopt = ''skip_encryption''',', @passwd =', cast(password AS varbinary(256)),', @sid =', sidFROM sysloginsWHERE name NOT IN ('sa')AND isntname = 0
Seems pretty straightforward.Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-28 : 13:07:26
|
| The script doesn't do anything but generate output. So yes run it and notice what it generates. The generated output is what you'd be running on the destination server.BTW, this should have been done prior to the restore on the new server.Tara Kizer |
|
|
|
sektor
Starting Member
14 Posts |
Posted - 2006-09-28 : 13:12:32
|
quote:
Originally posted by tkizer
The script doesn't do anything but generate output. So yes run it and notice what it generates. The generated output is what you'd be running on the destination server.BTW, this should have been done prior to the restore on the new server.Tara Kizer
Hmm. Well, I ran it and it just says in the results box below:"The command(s) completed successfully.That is all I see. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-28 : 13:20:31
|
| Did you run it on the old box?Tara Kizer |
|
|
|
sektor
Starting Member
14 Posts |
Posted - 2006-09-28 : 13:25:43
|
quote:
Originally posted by tkizer
Did you run it on the old box?Tara Kizer
Yes, that is correct. That is all it showed me.Maybe it won't work for me for some reason? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-28 : 13:32:20
|
| You must not have any accounts in syslogins then. Check master.dbo.syslogins on the old server to see what accounts you have in there. Are the accounts that you want to transfer SQL accounts or Windows accounts?Tara Kizer |
|
|
|
sektor
Starting Member
14 Posts |
Posted - 2006-09-28 : 13:36:19
|
quote:
Originally posted by tkizer
You must not have any accounts in syslogins then. Check master.dbo.syslogins on the old server to see what accounts you have in there.
Hmm. Ok. How do I check master.dbo.syslogs to check the accounts there? quote:
Are the accounts that you want to transfer SQL accounts or Windows accounts?Tara Kizer
Should be SQL accounts because each account we setup, we setup for SQL authentication. |
|
|
|
sektor
Starting Member
14 Posts |
Posted - 2006-09-28 : 14:18:52
|
| Ok...I think I might have figured out what is going on.Originally, I think when I ran the script in the analyzer, the drop down box at the top was selected on the actual database.I just ran it again after highlighting databases, then opening up the analyzer THEN running the script and I have a bunch of output in the GRIDS below.I see lots of stuff.I am assuming this is what I am looking for?What is the best way to save this so I can move it over to the new server?What is the best way to import this onto the new server?I appreciate the help very much.Sektor |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-28 : 14:34:11
|
| Run select * from master.dbo.syslogins in Query Analyzer to see if your users are in there. Do this on the old box.Tara Kizer |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-28 : 14:35:37
|
| You just copy the output from the results pane. Remove any lines that include accounts that you do not want to transfer. Paste the new output into the new box and hit F5.Tara Kizer |
|
|
|
sektor
Starting Member
14 Posts |
Posted - 2006-09-28 : 14:45:31
|
quote:
Originally posted by tkizer
Run select * from master.dbo.syslogins in Query Analyzer to see if your users are in there. Do this on the old box.Tara Kizer
Yep, they are.I'll go ahead and run the script again, copy it and move it over.Thanks for your help. You have been fantastic. |
|
|
|
sektor
Starting Member
14 Posts |
Posted - 2006-09-28 : 15:19:40
|
quote:
Originally posted by tkizer
You just copy the output from the results pane. Remove any lines that include accounts that you do not want to transfer. Paste the new output into the new box and hit F5.Tara Kizer
Ahh, you have been fantastic. I cannot thank you enough. Many many thanks. |
|
|
|
|
Copying/Moving user accounts...how?