September 5, 2006 at 12:21 pm
I always receive a error when attempting to use SQL 2005 SSIS to tranfer the logins from a SQL 2000 server to a SQL 2005 server. Is there a special or better way to do this?
September 6, 2006 at 12:57 am
you may want to try ...
select 'exec sp_addlogin ['
+ name
+ '],'
, password
, ', @encryptopt=skip_encryption'
from master..sysxlogins
run it on your sql2000
run the result on your sql2005
Be suspicious and TEST it !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 6, 2006 at 1:05 pm
You may also want to include the SID field to make sure your SQL logins are sychronized. Also exclude Windows logins (password is null) and sa (sid is 0x01). Just for fun, you might want to use the new CREATE LOGIN statement instead of sp_addlogin. I have duplicate entries on some servers for SQL logins, the duplicates (with incorrect SID or password) have a dbid value of 0 and should be excluded. (Possibly the result of creating logins in haste and then having to drop and re-add them later with the correct SID.)
SELECT
'CREATE LOGIN ' + quotename(name) + ' WITH PASSWORD = ', password, ' HASHED, sid=', sid,
', DEFAULT_DATABASE = ' + quotename(db_name(dbid)) + ', DEFAULT_LANGUAGE = ' + language
from master..sysxlogins
where sid <> 0x01 and dbid > 0 and password is not null
DEFAULT_DATABASE and DEFAULT_LANGUAGE are optional, remove the second line if it causes problems (such as a default database that doesn't exist on the new server).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply