分类： 数据库开发技术
2017-11-01 17:27:40
引言：当我们进行单个升级、迁移或进行SQL实例整机升级、迁移时，通常都会考虑登录名的迁移，因为登录名包含了所有的应用系统访问账号和管理员的管理账号等，所以在迁移时需要保证其登录名、密码、SID尽量不变，这样就能很好地规避孤立用户的问题，具体的迁移方法可以参考KB 246133和KB 918992。但是，当我们完成登录名的迁移后，我们发现这些登录名与服务器角色之间的对应关系不见了。此外，从SQL 2012开始有了自定义服务器角色，如果在旧服务器上创建了一些自定义角色，如何迁移这些服务器角色及其成员关系呢？
首先假设我们的SQL服务器A上已经存在一些登录名和自定义服务器角色，并且这些登录名和服务器角色之间存在一些隶属关系：
自定义服务器角色TestServerRole1，其成员包括一个SQL登录名TestLogin1
内置的服务器角色dbcreator，其成员包括一个SQL登录名TestLogin2
当我们需要进行数据库升级或迁移时，如果已经将登录名迁移到了新的SQL服务器B上，迁移后的登录名名称、密码和SID统统都没有变化；但是我们会发现有两点不足：
1. 自定义服务器角色没有迁移；
2. 所有服务器角色的成员都丢失；
下面我们将使用两个脚本分别实现自定义角色的迁移和角色成员关系的迁移。
运行SSMS，使用sysadmin角色身份登录到SQL实例A；
展开“安全性”→“服务器角色”，我们能看到前面9个都是系统内置的服务器角色，只有最后一个TestServerRole1是自定义角色；
接下来，我们新建查询，并执行下面的脚本：
SET NOCOUNT ON;
SELECT 'IF NOT EXISTS(SELECT name
FROM sys.server_principals
WHERE type = ''R'' AND name=''' + [name] + ''')
CREATE SERVER ROLE [' + [name] + '];'
FROM sys.server_principals
WHERE type = 'R'
AND principal_id > 10;
注意脚本的最后一句，这里我们为什么要筛选principal_id > 10呢？原因是principal_id为10以内的都是系统内置的服务器角色，这些角色在新的SQL实例上也都已经存在了，不需要迁移，所以把它们过滤掉。
脚本执行完成后，我们发现有1个结果，对应的正是我们刚才看到的那个自定义服务器角色；
将执行结果复制到新的SQL实例B上执行，将会自动创建一个同名的自定义服务器角色，如下图所示：
如果有多个自定义服务器角色，也能够通过上面的脚本一次性迁移。
但是，迁移成功后的服务器角色，我们发现其成员丢失了；不仅如此，内置的服务器角色dbcreator的成员也没有自动添加回来。
下面我们继续介绍如何迁移角色的成员关系。
同样，以sysadmin角色登录到SQL实例A，然后新建查询，执行下面的脚本导出服务器角色与登录名之间的成员关系：
SET NOCOUNT ON;
SELECT 'EXEC sp_addsrvrolemember @loginame = ''' + p.[name] +
''', @rolename = ''' + r.[name] + ''';'
FROM sys.server_principals AS p
JOIN sys.server_role_members AS srm
ON p.principal_id = srm.member_principal_id
JOIN sys.server_principals AS r
ON srm.role_principal_id = r.principal_id
WHERE p.[name] <> 'sa';
执行结果如下图所示，其中最后两条对应的就是前面我们介绍过的隶属关系。
将执行结果复制到新SQL实例B上执行，将会自动修复服务器角色与登录名之间的成员关系；但是前提是登录名必须先迁移完成，否则这些脚本将会报错找不到对应的登录名。
