SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Copying/Moving user accounts...how?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sektor
Starting Member

14 Posts

Posted - 09/27/2006 :  13:06:37  Show Profile  Reply with Quote
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

USA
36997 Posts

Posted - 09/27/2006 :  15:44:05  Show Profile  Visit tkizer's Homepage  Reply with Quote
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=16090&DisplayTab=Article

Tara Kizer
Go to Top of Page

SreenivasBora
Posting Yak Master

USA
164 Posts

Posted - 09/27/2006 :  16:33:19  Show Profile  Reply with Quote
1. Go to enterprise manager and extract the Login user script and execute on other server
OR
2. BCP out the logins info and BCP in on other server.

With Regards
BSR
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36997 Posts

Posted - 09/27/2006 :  16:35:47  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

sektor
Starting Member

14 Posts

Posted - 09/28/2006 :  12:42:57  Show Profile  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36997 Posts

Posted - 09/28/2006 :  12:45:33  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

sektor
Starting Member

14 Posts

Posted - 09/28/2006 :  13:01:06  Show Profile  Reply with Quote
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 ON
SELECT 'EXEC sp_addlogin @loginame = ''' + loginname + ''''
,', @defdb = ''' + dbname + ''''
,', @deflanguage = ''' + language + ''''
,', @encryptopt = ''skip_encryption'''
,', @passwd ='
, cast(password AS varbinary(256))
,', @sid ='
, sid
FROM syslogins
WHERE name NOT IN ('sa')
AND isntname = 0



Seems pretty straightforward.

Thanks
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36997 Posts

Posted - 09/28/2006 :  13:07:26  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

sektor
Starting Member

14 Posts

Posted - 09/28/2006 :  13:12:32  Show Profile  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36997 Posts

Posted - 09/28/2006 :  13:20:31  Show Profile  Visit tkizer's Homepage  Reply with Quote
Did you run it on the old box?

Tara Kizer
Go to Top of Page

sektor
Starting Member

14 Posts

Posted - 09/28/2006 :  13:25:43  Show Profile  Reply with Quote
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?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36997 Posts

Posted - 09/28/2006 :  13:32:20  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

sektor
Starting Member

14 Posts

Posted - 09/28/2006 :  13:36:19  Show Profile  Reply with Quote
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.
Go to Top of Page

sektor
Starting Member

14 Posts

Posted - 09/28/2006 :  14:18:52  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36997 Posts

Posted - 09/28/2006 :  14:34:11  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36997 Posts

Posted - 09/28/2006 :  14:35:37  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

sektor
Starting Member

14 Posts

Posted - 09/28/2006 :  14:45:31  Show Profile  Reply with Quote
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.
Go to Top of Page

sektor
Starting Member

14 Posts

Posted - 09/28/2006 :  15:19:40  Show Profile  Reply with Quote
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.

Edited by - sektor on 09/28/2006 15:20:00
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000