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.
| Author |
Topic |
|
rbohaty
Yak Posting Veteran
71 Posts |
 Posted - 2001-11-23 : 11:45:42
|
| Is there a way to transfer the user security from one SQL server to another? |
|
|
anastasia
Starting Member
6 Posts |
Posted - 2001-11-26 : 11:36:19
|
| Look here:http://support.microsoft.com/support/kb/articles/Q246/1/33.ASPand also at the following(I don't remember where I got this info):Using sp_addlogin to transfer passwordsOne little known feature in SQL 7 and 2000 enables you to port a password from one server to another without you actually knowing what the password is!Passwords are stored in the syslogins table in encrypted form – you can copy the encrypted password text from the original server into a sp_addlogin SQL command to run against the new server and indicate that the password is already encrypted using the @encryptopt = 'skip_encryption' – the login ID will then have the same password on both servers. Use this simple script so generate sp_addlogin commands to transfer logins to a new server.select 'sp_addlogin @loginame = x' + name + ', @passwd = "' + password + '", @encryptopt = skip_encryption' + char(13) + 'go'from sysloginswhere name in ('test1', 'test2') -- include specific logins onlyThe example script is minimalist, you can go further and include the default database and other security information. depending on your requirements and on your SQL Server version.Users of SQL Server 7 and 2000 should use @passwd = "N' + password + in the above script because passwords are double-byte character strings. |
 |
|
|
RocketScientist
Official SQLTeam Chef
85 Posts |
Posted - 2001-11-28 : 17:06:21
|
| If there's a chance you'll be copying databases over to the new server as well, you'll probably want to make sure to get the values in the SID column in syslogins as well as the passwords, and then use that value in sp_addlogin.--rs |
|
|
|
|
|
|
Transfer Security