August 11, 2005 at 11:55 am
I need to set up some kind of method of being sure a set of databases on a new server is kept updated with data from the old server until all the SQL Server -related apps are migrated over to the new server. I was going to use the Replication wizard to do that; however, the source instance is registered as Local. (Fortunately we had the foresight to register the other instance as something other than "Local.") Thus, the Replication Wizard says I can't set-up replication (at least using the Wizard).
So, what are my alternatives? Can I somehow rename the instance on the old server w/o affecting the applications (e.g., Great Plains) that run data bases on it?
Secondly, because of this same situation, I've been unable to use the process described in KB 246133 to migrate the user accounts over. Is there some other way I can do this (except the obvious way of checking users in one and resetting them in the new)?
August 12, 2005 at 1:08 am
Hi
I'm not quite sure about what you mean with 'regiterd as local'. I guess you've registered it in SQL Enterprise Manager (EM) as 'local'. Just delete the server in EM and reregister it using the servername.
Your question about the logins:
I always use this script. It generates new sp_addlogin and sp_grantlogin statements.
-- create EXEC sp_addlogin to migrate SQL logins including password and SID from one server to another
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
-- create EXEC sp_addlogin to migrate NT logins including SID from one server to another
SELECT 'EXEC sp_grantlogin @loginame = ''' + loginname + ''''
,' EXEC sp_defaultdb @loginame = ''' + loginname + ''''
,', @defdb = ''' + dbname + ''''
FROM syslogins
WHERE loginname NOT IN ('BUILTIN\Administrators')
AND isntname = 1
Hope this helps.
JP
August 12, 2005 at 6:31 am
JP,
Many thanks! It sure does!
Rich
August 12, 2005 at 1:29 pm
Here's the login transfer script the many others use:
http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;Q246133
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply