Jake Shelton (4/10/2014)
george sibbald (4/9/2014)
your question said transfer from sql2000 (2K) to sql2008 (2K8), is that correct?SQL IDs are logins authenticated by SQL, i.e. you enter an ID and a password, rather than windows authenticated logins where you just pass your domain ID credentials - so your instance is running in mixed mode.
Yep, the migration is from 2000 to 2008, and we are indeed running mixed mode. My question is whether I need to move the logins to a 2k5 box as an interim step before relocating again to 2008.
then you need the sp_help_revlogin_2000_to_2005 specifically that I posted (its the method2 in the link sqlbuddy referred to). SQL2005 and 2008 did not change so you can use this to go direct to 2008, you do not need an intermediate step.
run the code, copy the results over to your destination server, copy into SSMS and run in the logins you need. I presume this is a one off operation so no need to automate.
All you will be missing then potentially is any server roles and the default language setting. These two bits of code run on the SQL2000 server will reverse engineer those for you.
set quoted_identifier off
set nocount on
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'sysadmin'"
from syslogins where sysadmin = 1
union all
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'securityadmin'"
from syslogins where securityadmin = 1
union all
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'serveradmin'"
from syslogins where serveradmin = 1
union all
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'setupadmin'"
from syslogins where setupadmin = 1
union all
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'processadmin'"
from syslogins where processadmin = 1
union all
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'diskadmin'"
from syslogins where diskadmin = 1
union all
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'dbcreator'"
from syslogins where dbcreator = 1
union all
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'bulkadmin'"
from syslogins where bulkadmin = 1
set quoted_identifier off
select 'exec sp_defaultlanguage ',+"'" +loginname +"'," +language from syslogins
---------------------------------------------------------------------