合 MSSQL手动迁移Login账号、DBLink、AgentJOB等信息

发布日期 2021年11月29日 · 已更新 2021年12月27日

0 1127 4

Tags： MSSQL SQL Server DBLink MSSQL2000 用户迁移 AgentJOB迁移代理迁移元数据迁移 Login账号

👉 本文共约3260个字，系统预计阅读时间或需13分钟。

MSSQL迁移方案概览
操作场景
Login账号及其密码迁移
SQL Server 2012
SQL Server 2008
SQL Server 2000以下
删除登陆
DBLink连接
Agent JOB
关键配置

MSSQL迁移方案概览

云数据库SQL Server提供了多种数据迁移方案，可满足不同业务需求，使您可以在不影响业务的情况下平滑将数据库迁移至云数据库SQL Server。

迁移场景	文档链接
使用界面方式迁移SQL Server库表数据	通过数据管理服务导出SQL Server库表数据
云数据库备份文件迁移到云数据库SQL Server	云数据库SQL Server全量备份迁移到云数据库SQL Server
本地备份文件迁移到云数据库SQL Server	本地SQL Server备份文件迁移到云数据库SQL Server

操作场景

1、从本地或虚拟机通过DRS备份迁移功能直接迁移到本云RDS for SQL Server实例上，在迁移完成后还需要针对Login账号，DBLink，AgentJOB，关键配置进行识别，并手动完成相关同步工作。

2、通过备份恢复迁移SQL Server数据库到异机环境。

Login账号及其密码迁移

Login账号即SQL Server的实例级账号，主要用于用户管理用户服务器权限与数据库权限。一个用户通常会有多个该类型账号，用户迁移到RDS for SQL Server实例后，需要手动将自己本地的Login账号同步在实例上进行创建，以下方法将介绍如何在本云RDS for SQL Server实例上创建同名，同密码的Login账号，并进行授权操作。

SQL Server 2012

通过以下脚本获取本地实例Login账号创建脚本，获取到的脚本可以直接在目标端上执行，以创建同名，同密码的Login账号。

-- 基本语句
create login 登录名 with password = '密码',CHECK_EXPIRATION = OFF, CHECK_POLICY = ON, DEFAULT_DATABASE=[master];
create user 用户名 for login 登录名 with default_schema=dbo;
exec sp_addsrvrolemember @loginame = N'登录名', @rolename = N'sysadmin'（角色）;

-- 迁移login和用户
SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+
CASE
WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' + CONVERT(NVARCHAR(MAX),SL.password_hash,1)+ ' HASHED,SID=' +CONVERT(NVARCHAR(MAX),SP.SID,1)+',CHECK_EXPIRATION = '
+ CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = ' +CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END
ELSE ' FROM WINDOWS WITH'
END
+' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' as CreateLogin
FROM sys.server_principals AS SP LEFT JOIN sys.sql_logins AS SL
ON SP.principal_id = SL.principal_id
WHERE SP.type ='S'
AND SP.name NOT LIKE '##%##'
AND SP.name NOT LIKE 'NT AUTHORITY%'
AND SP.name NOT LIKE 'NT SERVICE%'
AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public')
UNION ALL
SELECT 'create user '+SL.name+' for login '+SL.name+' with default_schema=dbo;' as Createuser
FROM sys.sql_logins AS SL
WHERE SL.name NOT LIKE '##%##'
AND SL.name NOT LIKE 'NT AUTHORITY%'
AND SL.name NOT LIKE 'NT SERVICE%'
AND SL.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public')
UNION ALL
SELECT 'exec sp_addsrvrolemember @loginame = N'''+SL.name+''', @rolename = N''sysadmin'';' as rolemember
FROM sys.sql_logins AS SL
WHERE SL.name NOT LIKE '##%##'
AND SL.name NOT LIKE 'NT AUTHORITY%'
AND SL.name NOT LIKE 'NT SERVICE%'
AND SL.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public')
;

-- 获取密码hash值
SELECT loginname,CONVERT(VARBINARY(MAX), password),LOGINPROPERTY(loginname,'PASSWORDHASH')
  FROM syslogins 
  WHERE name = 'LHR'
;

-- 基本语句

create login 登录名 with password = '密码',CHECK_EXPIRATION = OFF, CHECK_POLICY = ON, DEFAULT_DATABASE=[master];

create user 用户名 for login 登录名 with default_schema=dbo;

exec sp_addsrvrolemember @loginame = N'登录名', @rolename = N'sysadmin'（角色）;

-- 迁移login和用户

SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+

CASE

WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' + CONVERT(NVARCHAR(MAX),SL.password_hash,1)+ ' HASHED,SID=' +CONVERT(NVARCHAR(MAX),SP.SID,1)+',CHECK_EXPIRATION = '

+ CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = ' +CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END

ELSE ' FROM WINDOWS WITH'

END

+' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' as CreateLogin

FROM sys.server_principals AS SP LEFT JOIN sys.sql_logins AS SL

ON SP.principal_id = SL.principal_id

WHERE SP.type ='S'

AND SP.name NOT LIKE '##%##'

AND SP.name NOT LIKE 'NT AUTHORITY%'

AND SP.name NOT LIKE 'NT SERVICE%'

AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public')

UNION ALL

SELECT 'create user '+SL.name+' for login '+SL.name+' with default_schema=dbo;' as Createuser

FROM sys.sql_logins AS SL

WHERE SL.name NOT LIKE '##%##'

AND SL.name NOT LIKE 'NT AUTHORITY%'

AND SL.name NOT LIKE 'NT SERVICE%'

AND SL.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public')

UNION ALL

SELECT 'exec sp_addsrvrolemember @loginame = N'''+SL.name+''', @rolename = N''sysadmin'';' as rolemember

FROM sys.sql_logins AS SL

WHERE SL.name NOT LIKE '##%##'

AND SL.name NOT LIKE 'NT AUTHORITY%'

AND SL.name NOT LIKE 'NT SERVICE%'

AND SL.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public')

;

-- 获取密码hash值

SELECT loginname,CONVERT(VARBINARY(MAX), password),LOGINPROPERTY(loginname,'PASSWORDHASH')

FROM syslogins

WHERE name = 'LHR'

;

将新建的Login账号跟用户当前RDS SQL Server实例上的迁移过来的数据库用户权限进行映射（mapping），以保证该账号在当前实例上的权限一致性，执行脚本如下。

declare @DBName nvarchar(200)
declare @Login_name nvarchar(200)
declare @SQL nvarchar(MAX)
set @Login_name = 'TestLogin7' //输入Login名称逐个执行
declare DBName_Cursor cursor for
select quotename(name)from sys.databases where  database_id > 4 and state = 0
and name not like '%$%'
and name <> 'rdsadmin'
open DBName_Cursor
fetch next from DBName_Cursor into @DBName
WHILE @@FETCH_STATUS= 0
begin
SET @SQL='    USE '+ (@DBName)+ '
if exists(select top 1 1 from sys.sysusers where name = '''+ @Login_Name +''')
begin
ALTER USER '+@Login_name+' with login = '+@Login_name+';
end
'
print @SQL
EXEC (@SQL)
fetch next from DBName_Cursor into @DBName
end
close DBName_Cursor
deallocate DBName_Cursor

declare @DBName nvarchar(200)

declare @Login_name nvarchar(200)

declare @SQL nvarchar(MAX)

set @Login_name = 'TestLogin7' //输入Login名称逐个执行

declare DBName_Cursor cursor for

select quotename(name)from sys.databases where database_id > 4 and state = 0

and name not like '%$%'

and name <> 'rdsadmin'

open DBName_Cursor

fetch next from DBName_Cursor into @DBName

WHILE @@FETCH_STATUS= 0

begin

SET @SQL=' USE '+ (@DBName)+ '

if exists(select top 1 1 from sys.sysusers where name = '''+ @Login_Name +''')

begin

ALTER USER '+@Login_name+' with login = '+@Login_name+';

end

print @SQL

EXEC (@SQL)

fetch next from DBName_Cursor into @DBName

end

close DBName_Cursor

deallocate DBName_Cursor

说明：

以上脚本执行完成后，用户即可在自己的新实例上看到同名的登录账号，并且密码跟权限是完全跟本地一致的。

SQL Server 2008

-- 基本语句
create login 登录名 with password = '密码',CHECK_EXPIRATION = OFF, CHECK_POLICY = ON, DEFAULT_DATABASE=[master];
create user 用户名 for login 登录名 with default_schema=dbo;
exec sp_addsrvrolemember @loginame = N'登录名', @rolename = N'sysadmin'（角色）;

-- 获取密码hash值
SELECT loginname,CONVERT(VARBINARY(32), password),LOGINPROPERTY(loginname,'PASSWORDHASH')
   FROM syslogins 
   WHERE name = 'LHR'
;
alter login lhr6 with password = 0x0100301E99CB10A532DB23EC18967DDE9527E0BBD33808DD1A29  HASHED,CHECK_POLICY = OFF;

-- 迁移login和用户
SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+
CASE
WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ''' + SP.name + ''' ,SID=' +CONVERT(NVARCHAR(MAX),SP.SID,1)+',CHECK_EXPIRATION = '
+ CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = ' +CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END
ELSE ' FROM WINDOWS WITH'
END
+' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' as CreateLogin
FROM sys.server_principals AS SP LEFT JOIN sys.sql_logins AS SL
ON SP.principal_id = SL.principal_id
WHERE SP.type ='S'
AND SP.name NOT LIKE '##%##'
AND SP.name NOT LIKE 'NT AUTHORITY%'
AND SP.name NOT LIKE 'NT SERVICE%'
AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public')
UNION ALL
SELECT 'create user '+SL.name+' for login '+SL.name+' with default_schema=dbo;' as Createuser
FROM sys.sql_logins AS SL
WHERE SL.name NOT LIKE '##%##'
AND SL.name NOT LIKE 'NT AUTHORITY%'
AND SL.name NOT LIKE 'NT SERVICE%'
AND SL.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public')
UNION ALL
SELECT 'exec sp_addsrvrolemember @loginame = N'''+SL.name+''', @rolename = N''sysadmin'';' as rolemember
FROM sys.sql_logins AS SL
WHERE SL.name NOT LIKE '##%##'
AND SL.name NOT LIKE 'NT AUTHORITY%'
AND SL.name NOT LIKE 'NT SERVICE%'
AND SL.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public')
;

-- 更新密码
SELECT 'alter login '+sl.name+'  with password = ',CONVERT(VARBINARY(32), password),' HASHED,CHECK_POLICY = OFF;' as rolemember
FROM syslogins AS SL
WHERE SL.name NOT LIKE '##%##'
AND SL.name NOT LIKE 'NT AUTHORITY%'
AND SL.name NOT LIKE 'NT SERVICE%'
AND SL.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public');

-- 数据库映射
USE [lhrdb]
sp_change_users_login 'update_one', 'lhr', 'lhr';

-- 基本语句

create login 登录名 with password = '密码',CHECK_EXPIRATION = OFF, CHECK_POLICY = ON, DEFAULT_DATABASE=[master];

create user 用户名 for login 登录名 with default_schema=dbo;

exec sp_addsrvrolemember @loginame = N'登录名', @rolename = N'sysadmin'（角色）;

-- 获取密码hash值

SELECT loginname,CONVERT(VARBINARY(32), password),LOGINPROPERTY(loginname,'PASSWORDHASH')

FROM syslogins

WHERE name = 'LHR'

;

alter login lhr6 with password = 0x0100301E99CB10A532DB23EC18967DDE9527E0BBD33808DD1A29 HASHED,CHECK_POLICY = OFF;

-- 迁移login和用户

SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+

CASE

WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ''' + SP.name + ''' ,SID=' +CONVERT(NVARCHAR(MAX),SP.SID,1)+',CHECK_EXPIRATION = '

+ CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = ' +CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END

ELSE ' FROM WINDOWS WITH'

END

+' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' as CreateLogin

FROM sys.server_principals AS SP LEFT JOIN sys.sql_logins AS SL

ON SP.principal_id = SL.principal_id

WHERE SP.type ='S'

AND SP.name NOT LIKE '##%##'

AND SP.name NOT LIKE 'NT AUTHORITY%'

AND SP.name NOT LIKE 'NT SERVICE%'

AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public')

UNION ALL

SELECT 'create user '+SL.name+' for login '+SL.name+' with default_schema=dbo;' as Createuser

FROM sys.sql_logins AS SL

WHERE SL.name NOT LIKE '##%##'

AND SL.name NOT LIKE 'NT AUTHORITY%'

AND SL.name NOT LIKE 'NT SERVICE%'

AND SL.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public')

UNION ALL

SELECT 'exec sp_addsrvrolemember @loginame = N'''+SL.name+''', @rolename = N''sysadmin'';' as rolemember

FROM sys.sql_logins AS SL

WHERE SL.name NOT LIKE '##%##'

AND SL.name NOT LIKE 'NT AUTHORITY%'

AND SL.name NOT LIKE 'NT SERVICE%'

AND SL.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public')

;

-- 更新密码

SELECT 'alter login '+sl.name+' with password = ',CONVERT(VARBINARY(32), password),' HASHED,CHECK_POLICY = OFF;' as rolemember

FROM syslogins AS SL

WHERE SL.name NOT LIKE '##%##'

AND SL.name NOT LIKE 'NT AUTHORITY%'

AND SL.name NOT LIKE 'NT SERVICE%'

AND SL.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public');

-- 数据库映射

USE [lhrdb]

sp_change_users_login 'update_one', 'lhr', 'lhr';

SQL Server 2000以下

若是SQL Server 2000版本，则可以使用如下脚本，如下：

   -- 基本SQL
   -- sp_addlogin   '登录名','密码';
   sp_addlogin 'USER','PWD','DATABASE';
   go
   sp_adduser '登录名','用户名','db_owner'（组名）;
   go
   sp_addsrvrolemember '用户名','sysadmin' （角色名）;
   go

   -- LOGIN，记得加上G
   SELECT  1 AS ID,loginname,'sp_addlogin '''+ sp.loginname + ''',', CONVERT(VARBINARY(32), password),','''+sp.dbname+''';' 
   FROM syslogins AS SP
   WHERE SP.name NOT LIKE '##%##'
   AND SP.name NOT LIKE 'NT AUTHORITY%'
   AND SP.name NOT LIKE 'NT SERVICE%'
   AND SP.name NOT LIKE '%Administrators%'
   AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public','sa')
   ;

   -- 用户
   SELECT 2 AS ID,NAME,'sp_adduser '''+ sp.NAME + ''','''+ sp.NAME+''',''db_owner'';' 
   FROM syslogins AS SP
   WHERE SP.name NOT LIKE '##%##'
   AND SP.name NOT LIKE 'NT AUTHORITY%'
   AND SP.name NOT LIKE 'NT SERVICE%'
   AND SP.name NOT LIKE '%Administrators%'
   AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public','sa')

   union all
   SELECT 3 AS ID,NAME,'GO' 
   FROM syslogins AS SP
   WHERE SP.name NOT LIKE '##%##'
   AND SP.name NOT LIKE 'NT AUTHORITY%'
   AND SP.name NOT LIKE 'NT SERVICE%'
   AND SP.name NOT LIKE '%Administrators%'
   AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public','sa')

   union all
   SELECT 4 AS ID,NAME,'sp_addsrvrolemember '''+ sp.NAME + ''',''sysadmin'';' 
   FROM syslogins AS SP
   WHERE SP.name NOT LIKE '##%##'
   AND SP.name NOT LIKE 'NT AUTHORITY%'
   AND SP.name NOT LIKE 'NT SERVICE%'
   AND SP.name NOT LIKE '%Administrators%'
   AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public','sa')

   union all
   SELECT 5  AS ID,NAME,'GO'  
   FROM syslogins AS SP
   WHERE SP.name NOT LIKE '##%##'
   AND SP.name NOT LIKE 'NT AUTHORITY%'
   AND SP.name NOT LIKE 'NT SERVICE%'
   AND SP.name NOT LIKE '%Administrators%'
   AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public','sa')

   ORDER BY NAME,ID
   ;

   -- 数据库映射
   USE [lhrdb]
   sp_change_users_login 'update_one', 'lhr', 'lhr';

   select 0 id,name,'use '+name+';' from master..sysdatabases where name not in ('master','tempdb','model','msdb')

   union all
   select 1 id,name,'GO ' from master..sysdatabases where name not in ('master','tempdb','model','msdb')

   union all
   select 2 id,name,'sp_change_users_login ''update_one'', ''user_data'', ''user_data''; ' from master..sysdatabases where name not in ('master','tempdb','model','msdb')

   union all
   select 3 id,name,'GO ' from master..sysdatabases where name not in ('master','tempdb','model','msdb')

   ORDER BY NAME,ID
   ;

-- 基本SQL

-- sp_addlogin '登录名','密码';

sp_addlogin 'USER','PWD','DATABASE';

sp_adduser '登录名','用户名','db_owner'（组名）;

sp_addsrvrolemember '用户名','sysadmin' （角色名）;

-- LOGIN，记得加上G

SELECT 1 AS ID,loginname,'sp_addlogin '''+ sp.loginname + ''',', CONVERT(VARBINARY(32), password),','''+sp.dbname+''';'

FROM syslogins AS SP

WHERE SP.name NOT LIKE '##%##'

AND SP.name NOT LIKE 'NT AUTHORITY%'

AND SP.name NOT LIKE 'NT SERVICE%'

AND SP.name NOT LIKE '%Administrators%'

AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public','sa')

;

-- 用户

SELECT 2 AS ID,NAME,'sp_adduser '''+ sp.NAME + ''','''+ sp.NAME+''',''db_owner'';'

FROM syslogins AS SP

WHERE SP.name NOT LIKE '##%##'

AND SP.name NOT LIKE 'NT AUTHORITY%'

AND SP.name NOT LIKE 'NT SERVICE%'

AND SP.name NOT LIKE '%Administrators%'

AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public','sa')

union all

SELECT 3 AS ID,NAME,'GO'

FROM syslogins AS SP

WHERE SP.name NOT LIKE '##%##'

AND SP.name NOT LIKE 'NT AUTHORITY%'

AND SP.name NOT LIKE 'NT SERVICE%'

AND SP.name NOT LIKE '%Administrators%'

AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public','sa')

union all

SELECT 4 AS ID,NAME,'sp_addsrvrolemember '''+ sp.NAME + ''',''sysadmin'';'

FROM syslogins AS SP

WHERE SP.name NOT LIKE '##%##'

AND SP.name NOT LIKE 'NT AUTHORITY%'

AND SP.name NOT LIKE 'NT SERVICE%'

AND SP.name NOT LIKE '%Administrators%'

AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public','sa')

union all

SELECT 5 AS ID,NAME,'GO'

FROM syslogins AS SP

WHERE SP.name NOT LIKE '##%##'

AND SP.name NOT LIKE 'NT AUTHORITY%'

AND SP.name NOT LIKE 'NT SERVICE%'

AND SP.name NOT LIKE '%Administrators%'

AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public','sa')

ORDER BY NAME,ID

;

-- 数据库映射

USE [lhrdb]

sp_change_users_login 'update_one', 'lhr', 'lhr';

select 0 id,name,'use '+name+';' from master..sysdatabases where name not in ('master','tempdb','model','msdb')

union all

select 1 id,name,'GO ' from master..sysdatabases where name not in ('master','tempdb','model','msdb')

union all

select 2 id,name,'sp_change_users_login ''update_one'', ''user_data'', ''user_data''; ' from master..sysdatabases where name not in ('master','tempdb','model','msdb')

union all

select 3 id,name,'GO ' from master..sysdatabases where name not in ('master','tempdb','model','msdb')

ORDER BY NAME,ID

;

删除登陆


-- 删除
sp_dropuser 'ac';
GO
sp_droplogin 'ac';
GO

SELECT  1  AS ID,loginname,'sp_dropuser ' + sp.loginname +';'
FROM syslogins AS SP
WHERE SP.name NOT LIKE '##%##'
AND SP.name NOT LIKE 'NT AUTHORITY%'
AND SP.name NOT LIKE 'NT SERVICE%'
AND SP.name NOT LIKE '%Administrators%'
AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public','sa')

union all
SELECT  2  AS ID,loginname,'GO'
FROM syslogins AS SP
WHERE SP.name NOT LIKE '##%##'
AND SP.name NOT LIKE 'NT AUTHORITY%'
AND SP.name NOT LIKE 'NT SERVICE%'
AND SP.name NOT LIKE '%Administrators%'
AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public','sa')

union all
SELECT  3  AS ID,loginname,'sp_droplogin ' + sp.loginname +';'
FROM syslogins AS SP
WHERE SP.name NOT LIKE '##%##'
AND SP.name NOT LIKE 'NT AUTHORITY%'
AND SP.name NOT LIKE 'NT SERVICE%'
AND SP.name NOT LIKE '%Administrators%'
AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public','sa')

union all
SELECT  4  AS ID,loginname,'GO'
FROM syslogins AS SP
WHERE SP.name NOT LIKE '##%##'
AND SP.name NOT LIKE 'NT AUTHORITY%'
AND SP.name NOT LIKE 'NT SERVICE%'
AND SP.name NOT LIKE '%Administrators%'
AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public','sa')

ORDER BY loginname,ID

;

-- 删除

sp_dropuser 'ac';

sp_droplogin 'ac';

SELECT 1 AS ID,loginname,'sp_dropuser ' + sp.loginname +';'

FROM syslogins AS SP

WHERE SP.name NOT LIKE '##%##'

AND SP.name NOT LIKE 'NT AUTHORITY%'

AND SP.name NOT LIKE 'NT SERVICE%'

AND SP.name NOT LIKE '%Administrators%'

AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public','sa')

union all

SELECT 2 AS ID,loginname,'GO'

FROM syslogins AS SP

WHERE SP.name NOT LIKE '##%##'

AND SP.name NOT LIKE 'NT AUTHORITY%'

AND SP.name NOT LIKE 'NT SERVICE%'

AND SP.name NOT LIKE '%Administrators%'

AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public','sa')

union all

SELECT 3 AS ID,loginname,'sp_droplogin ' + sp.loginname +';'

FROM syslogins AS SP

WHERE SP.name NOT LIKE '##%##'

AND SP.name NOT LIKE 'NT AUTHORITY%'

AND SP.name NOT LIKE 'NT SERVICE%'

AND SP.name NOT LIKE '%Administrators%'

AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public','sa')

union all

SELECT 4 AS ID,loginname,'GO'

FROM syslogins AS SP

WHERE SP.name NOT LIKE '##%##'

AND SP.name NOT LIKE 'NT AUTHORITY%'

AND SP.name NOT LIKE 'NT SERVICE%'

AND SP.name NOT LIKE '%Administrators%'

AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public','sa')

ORDER BY loginname,ID

;

DBLink连接

DBLink连接指SQL Server支持用户通过创建DBLink连接的方式，跟外部实例上的数据库进行交互，这种方式可以极大的方便用户不同实例间，不同数据库类型之间的数据库查询，同步，比较，所以大部分用户都会在本地实例上用到该服务，但是迁移上云后，本地DBLink是不会自动同步到云上实例的，还需要简单的手动进行同步。

通过微软提供的官方Microsoft SQL Server Management Studio客户端工具连接本地实例与云上实例，同时在“服务器对象 > 链接服务器”下找到当前实例的DBLink链接。
图2 查看DBLink链接
选中链接服务器，然后按F7，会自动弹出对象资源管理信息页，在该页面中可以方便你快速的自动创建脚本。
图3 自动创建脚本

在新窗口中，可以看到当前实例上所有DBLink的创建脚本，仅需复制该脚本到目标实例上，并修改@rmtpassword上的密码即可执行创建操作。

USE [master]
GO

/****** Object:  LinkedServer [DRS_TEST_REMOTE]    Script Date: 2019/5/25 17:51:50 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'DRS_TEST_REMOTE', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'DESKTOP-B18JH5T\SQLSERVER2016EE'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DRS_TEST_REMOTE',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='########'
GO

USE [master]

/****** Object: LinkedServer [DRS_TEST_REMOTE] Script Date: 2019/5/25 17:51:50 ******/

EXEC master.dbo.sp_addlinkedserver @server = N'DRS_TEST_REMOTE', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'DESKTOP-B18JH5T\SQLSERVER2016EE'

/* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DRS_TEST_REMOTE',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='########'

说明：

以上脚本为范例，创建的脚本可能包含大量系统默认配置项，但是每个DBLink仅需保留以下两个关键脚本即可执行成功，同时需要注意重新输入账号连接密码。

SQL Server 2000可能会报错“有出现过:"消息 15429，级别 16，状态 1，过程 sp_addlinkedserver，第 42 行'(null)' 是无效的产品名称。”

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

获取验证码：请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

打赏赞(4)

标签： MSSQL SQL Server DBLink MSSQL2000 用户迁移 AgentJOB迁移代理迁移元数据迁移 Login账号

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

1、声明：本网站有部分文章整合或转载自网络，文章著作权归属原作者或原团队；若有侵权，深表歉意，请联系本站站长进行处理，谢谢！！！
2、本站提供数据库的培训和考证业务，培训包含但不限于Oracle、MySQL、PostgreSQL、SQL Server、国产数据库等，考证包括但不限于Oracle OCP、Oracle OCM、MySQL OCP、PGCA、PGCE、PGCM等，私聊QQ646634621或微信dbaup66，非诚勿扰，谢谢！！！
3、麦老师新建了《西安DBA数据架构师》微信群，有志同道合的朋友可以私聊我入群，我们一起聊技术，聊生活，空了可以喝酒约饭。
4、为了方便大家更有针对性的讨论学习数据库，故新建数据库分类群，包括Oracle群、MySQL群、SQL Server群、PG&GP群、国产&信创群。志同道合的朋友可以入群聊技术聊人生，捣乱的勿进，喷子勿进，极端人士勿进，谢谢。

合 MSSQL手动迁移Login账号、DBLink、AgentJOB等信息

MSSQL迁移方案概览

操作场景

Login账号及其密码迁移

SQL Server 2012

SQL Server 2008

SQL Server 2000以下

删除登陆

DBLink连接

相关文章

您可能还喜欢...

发表回复取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

2024 年 11 月
一	二	三	四	五	六	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

合 MSSQL手动迁移Login账号、DBLink、AgentJOB等信息

MSSQL迁移方案概览

操作场景

Login账号及其密码迁移

SQL Server 2012

SQL Server 2008

SQL Server 2000以下

删除登陆

DBLink连接

相关文章

您可能还喜欢...

MSSQL中的等待事件OLEDB说明

数据库“tempdb”的事务日志已满，原因为“ACTIVE_TRANSACTION” 解决方法

在Docker中快速使用SQL Server各个版本（MSSQL）的数据库

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

发表回复取消回复