Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 is it safe to script out SQL user accounts, passwo

Author  Topic 

beachtime1999
Starting Member

25 Posts

Posted - 2007-08-03 : 09:19:07
Hello
is it safe to script out SQL user accounts, passwords and permissions?

can i Just copy data from one sysuser to another?
how can I transfer the passwords and permission?

select * from sysuser and where isnull(sid,1)<>1 and
sid <> '0x00'--guest
and sid <> '0x01'--

beachtime1999
Starting Member

25 Posts

Posted - 2007-08-03 : 09:52:34
Can I copy the data from syslogin to new server?
Go to Top of Page

beachtime1999
Starting Member

25 Posts

Posted - 2007-08-03 : 09:52:34
Can I copy the data from syslogin to new server?
Go to Top of Page

beachtime1999
Starting Member

25 Posts

Posted - 2007-08-03 : 09:52:34
Can I copy the data from syslogin to new server?
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-03 : 10:34:28
Yes, use copy logins task in sql2k dts or in sql2k5 ssis.
Go to Top of Page

beachtime1999
Starting Member

25 Posts

Posted - 2007-08-03 : 13:58:12
Great Thanks a bundle.
Steve
Go to Top of Page

stephe40
Posting Yak Master

218 Posts

Posted - 2007-08-03 : 15:25:36
SMO will not script out login passwords. It will generate code to create the login with a random password. Which sucks, because I used DMO in the past to script out logins with the encrypted passwords, as a disaster recovery measure.

- Eric
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-03 : 15:35:13
Just use this: http://www.sqlmag.com/articles/index.cfm?articleid=16090&

It grabs the login id, password, and sid information. You just need to run it on the 2000 server, copy the output, possibly edit out some logins, then paste the code into your 2005 server and run it. All logins and passwords are now on your 2005 server and you don't even need to unorphan them since the sid was copied over.

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-08-04 : 07:02:08
is there a way to grab the password in 2005 like in 2000?
i get random characters generated

--------------------
keeping it simple...
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-04 : 20:18:33
SSIS package copies password over.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-05 : 00:08:05
The link that I posted works for 2005 too. It grabs the passwords plus sids.

I hate using external packages for logins when a simple script can do it.

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -