在数据库的管理过程中，除了要创建SQL Server登陆账户，也会经常遇到需要克隆账户的情形，如服务器硬件老化，需要更新换代；或者需要SQL Server新版本的功能，将SQL Server更新到更高版本。这些都需要将一个服务器上的数据库转移到另外一个服务器上，像这样的数据库跨服务器的转移，不可避免的涉及到登陆账户的转移（数据库用户转移随着备份的还原直接转移了），转移不仅要保证账户相同，账户密码、账户权限也要相同，这样才能最大程度减少程序变更，减少停机时间。对于大量的登陆账户的转移，这是一件较为麻烦的事情，本文将给出克隆实例上登陆账户、密码及权限的脚本，让你一键解决这些烦恼。下面先给出克隆单个登陆账户、密码及权限的脚本：
USE master
GO
/*
功能：生成单个登陆帐户及权限的创建脚本
适用于SQL SERVER 2008/SQL SERVER 2008 R2/SQL SERVER 2012/SQL SERVER 2014/SQL SERVER 2016
***其他版本暂未测试
注意：1.适用于同版本或低版本升级高版本SQL Server 数据库时登陆帐户的迁移
2.不能克隆凭据、非对称密钥和证书
DECLARE @sql VARCHAR(max)
EXEC sp_DBA_LoginClone [Jack],@sql OUTPUT
SELECT @sql FOR XML PATH('')
*/
CREATE PROC sp_DBA_LoginClone
@loginName sysname
,@loginCloneSQL VARCHAR(max) OUTPUT
AS
BEGIN
DECLARE @password_hash VARchar(256)
DECLARE @sid VARchar(85)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled INT
DECLARE @is_policy_checked VARCHAR(3)
DECLARE @is_expiration_checked VARCHAR(3)
DECLARE @defaultdb sysname
DECLARE @type varchar (1)
--判断登陆名是否存在
IF NOT EXISTS (
SELECT 1 FROM sys.syslogins WHERE name= @loginName
)
BEGIN
PRINT @loginName + ' 不存在'
RETURN
END
SELECT @type = type
FROM sys.server_principals
WHERE name=@loginName
SELECT @hasaccess=hasaccess, @denylogin=denylogin
FROM sys.syslogins
WHERE name=@loginName
SET @loginCloneSQL = 'USE master ' + CHAR(10) + 'GO ' + CHAR(10)
--生成登陆名创建脚本
IF (@type IN ( 'G', 'U'))
BEGIN --NT authenticated account/group
SELECT @defaultdb=default_database_name
from sys.server_principals
WHERE name=@loginName
SET @loginCloneSQL =@loginCloneSQL+ 'Create LOGIN '
+ QUOTENAME( @loginName,']' )
+ ' FROM WINDOWS WITH DEFAULT_DATABASE = '
+ QUOTENAME(@defaultdb , ']')+CHAR(10)
END
ELSE
BEGIN
SELECT @sid=CONVERT(VARCHAR(85),p.sid,1), @is_disabled= p.is_disabled
, @defaultdb=p.default_database_name
,@is_policy_checked= CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' end
,@is_expiration_checked= CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' END
,@password_hash=CONVERT(VARCHAR(256),password_hash , 1)
FROM sys.sql_logins p
WHERE name=@loginName
SET @loginCloneSQL=@loginCloneSQL+' CREATE LOGIN '+QUOTENAME(@loginName, ']')+CHAR(10)
+' WITH PASSWORD='+@password_hash+ ' HASHED '+CHAR(10)
+',SID='+@sid +CHAR(10)
+', DEFAULT_DATABASE = ' + QUOTENAME(@defaultdb, ']')+CHAR(10)
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @loginCloneSQL = @loginCloneSQL + ', CHECK_POLICY = ' + @is_policy_checked+CHAR(10)
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @loginCloneSQL = @loginCloneSQL + ', CHECK_EXPIRATION = ' + @is_expiration_checked+CHAR(10)
END
END
--IF (@denylogin = 1)
--BEGIN --login is denied access
-- SET @loginCloneSQL = @loginCloneSQL + ' DENY CONNECT SQL TO ' + QUOTENAME( @loginName )+CHAR(10)
--END
--ELSE IF (@hasaccess = 0)
--BEGIN --login exists but does not have access
-- SET @loginCloneSQL = @loginCloneSQL + ' REVOKE CONNECT SQL TO ' + QUOTENAME( @loginName )+CHAR(10)
--END
IF (@is_disabled = 1)
BEGIN --login is disabled
SET @loginCloneSQL = @loginCloneSQL + ' Alter LOGIN ' + QUOTENAME( @loginName ) + 'DISABLE' +CHAR(10)
END
DECLARE @versionNum AS INT
SET @versionNum = SUBSTRING(@@VERSION, 22, 4)
IF @versionNum >= 2012
BEGIN
SELECT @loginCloneSQL = ISNULL(@loginCloneSQL, '')
+ ISNULL(STUFF((SELECT
CASE WHEN ISNULL(ISNULL(o.name, e.name), ag.name) IS NULL
AND sp.state <> 'W'
THEN sp.state_desc COLLATE Chinese_PRC_CI_AS + ' ' + sp.permission_name
+ ' TO ' + QUOTENAME(s.name, '[') + CHAR(10)
WHEN ISNULL(ISNULL(o.name, e.name), ag.name) IS NULL
AND sp.state = 'W'
THEN 'GRANT ' + sp.permission_name + ' TO ' + QUOTENAME(s.name, '[')
+ ' ' + ' WITH GRANT OPTION' + CHAR(10)
WHEN ISNULL(ISNULL(o.name, e.name), ag.name) IS NOT NULL
AND class = 101
AND sp.state <> 'W'
THEN sp.state_desc COLLATE Chinese_PRC_CI_AS + ' ' + sp.permission_name
+ ' ON LOGIN::' + QUOTENAME(o.name, '[') + ' TO '
+ QUOTENAME(s.name, '[') + CHAR(10)
WHEN ISNULL(ISNULL(o.name, e.name), ag.name) IS NOT NULL
AND class = 101
AND sp.state = 'W'
THEN 'GRANT ' + sp.permission_name + ' ON LOGIN::' + QUOTENAME(o.name,
'[') + ' TO '
+ QUOTENAME(s.name, '[') + ' ' + ' WITH GRANT OPTION' + CHAR(10)
WHEN ISNULL(ISNULL(o.name, e.name), ag.name) IS NOT NULL
AND class = 105
AND sp.state <> 'W'
THEN sp.state_desc + ' ' + sp.permission_name + ' ON ENDPOINT::'
+ QUOTENAME(e.name, '[') + ' TO ' + QUOTENAME(s.name, '[')
+ CHAR(10)
WHEN ISNULL(ISNULL(o.name, e.name), ag.name) IS NOT NULL
AND class = 105
AND sp.state = 'W'
THEN 'GRANT ' + sp.permission_name + ' ON ENDPOINT::'
+ QUOTENAME(e.name, '[') + ' TO ' + QUOTENAME(s.name, '[') + ' '
+ ' WITH GRANT OPTION' + CHAR(10)
WHEN ISNULL(ISNULL(o.name, e.name), ag.name) IS NOT NULL
AND class = 108
AND sp.state <> 'W'
THEN sp.state_desc + ' ' + sp.permission_name + ' ON ENDPOINT::'
+ QUOTENAME(ag.name, '[') + ' TO ' + QUOTENAME(s.name, '[')
+ CHAR(10)
WHEN ISNULL(ISNULL(o.name, e.name), ag.name) IS NOT NULL
AND class = 108
AND sp.state = 'W'
THEN 'GRANT ' + sp.permission_name + ' ON AVAILABILITY GROUP::'
+ QUOTENAME(ag.name, '[') + ' TO ' + QUOTENAME(s.name, '[') + ' '
+ ' WITH GRANT OPTION' + CHAR(10)
END
FROM sys.server_permissions sp
LEFT JOIN sys.server_principals p ON p.principal_id = sp.grantor_principal_id
LEFT JOIN sys.server_principals o ON sp.major_id = o.principal_id
AND class_desc = 'SERVER_PRINCIPAL'
LEFT JOIN sys.server_principals s ON s.principal_id = sp.grantee_principal_id
LEFT JOIN sys.endpoints e ON e.endpoint_id = sp.major_id
AND class_desc = 'ENDPOINT'
LEFT JOIN sys.availability_replicas ar ON ar.replica_metadata_id = sp.major_id
AND replica_metadata_id IS NOT NULL
LEFT JOIN sys.availability_groups ag ON ag.group_id = ar.group_id
WHERE s.name = @loginName
FOR XML PATH('') ,
TYPE).value('.', 'varchar(max)'), 1, 0, ''), '')
END
ELSE
BEGIN
SELECT @loginCloneSQL = ISNULL(@loginCloneSQL, '')
+ ISNULL(STUFF((
SELECT CASE WHEN ISNULL(o.name, e.name) IS NULL
AND sp.state <> 'W'
THEN sp.state_desc COLLATE Chinese_PRC_CI_AS + ' ' + sp.permission_name
+ ' TO ' + QUOTENAME(s.name, '[') + CHAR(10)
WHEN ISNULL(o.name, e.name) IS NULL
AND sp.state = 'W'
THEN 'GRANT ' + sp.permission_name + ' TO ' + QUOTENAME(s.name, '[')
+ ' ' + ' WITH GRANT OPTION' + CHAR(10)
WHEN ISNULL(o.name, e.name) IS NOT NULL
AND class = 101
AND sp.state <> 'W'
THEN sp.state_desc COLLATE Chinese_PRC_CI_AS + ' ' + sp.permission_name
+ ' ON LOGIN::' + QUOTENAME(o.name, '[') + ' TO '
+ QUOTENAME(s.name, '[') + CHAR(10)
WHEN ISNULL(o.name, e.name) IS NOT NULL
AND class = 101
AND sp.state = 'W'
THEN 'GRANT ' + sp.permission_name + ' ON LOGIN::'
+ QUOTENAME(o.name,'[') + ' TO '
+ QUOTENAME(s.name, '[') + ' ' + ' WITH GRANT OPTION' + CHAR(10)
WHEN ISNULL(o.name, e.name) IS NOT NULL
AND class = 105
AND sp.state <> 'W'
THEN sp.state_desc + ' ' + sp.permission_name + ' ON ENDPOINT::'
+ QUOTENAME(e.name, '[') + ' TO ' + QUOTENAME(s.name, '[')
+ CHAR(10)
WHEN ISNULL(o.name, e.name) IS NOT NULL
AND class = 105
AND sp.state = 'W'
THEN 'GRANT ' + sp.permission_name + ' ON ENDPOINT::'
+ QUOTENAME(e.name, '[') + ' TO ' + QUOTENAME(s.name, '[') + ' '
+ ' WITH GRANT OPTION' + CHAR(10)
END
FROM sys.server_permissions sp
LEFT JOIN sys.server_principals p ON p.principal_id = sp.grantor_principal_id
LEFT JOIN sys.server_principals o ON sp.major_id = o.principal_id
AND class_desc = 'SERVER_PRINCIPAL'
LEFT JOIN sys.server_principals s ON s.principal_id = sp.grantee_principal_id
LEFT JOIN sys.endpoints e ON e.endpoint_id = sp.major_id
AND class_desc = 'ENDPOINT'
WHERE s.name = @loginName
FOR XML PATH('') ,
TYPE).value('.', 'varchar(max)'), 1, 0, ''), '')
END
--登陆名所属角色（包括用户创建的服务器角色）
SELECT @loginCloneSQL = ISNULL(@loginCloneSQL, '')
+ ISNULL(STUFF((SELECT 'EXEC sp_addsrvrolemember ' + QUOTENAME(member.name,'[') + ',' + role.name + CHAR(10)
FROM sys.server_role_members svrm
JOIN sys.server_principals AS role ON svrm.role_principal_id = role.principal_id
JOIN sys.server_principals AS member ON svrm.member_principal_id = member.principal_id
WHERE member.name = @loginName
FOR XML PATH('') ,
TYPE).value('.', 'varchar(max)'), 1, 0, ''), '')
SELECT @loginCloneSQL=@loginCloneSQL+' '
END下面我们先在SQL Server 2008 上创建测试环境，测试登陆账户克隆脚本的正确性：
--创建SQL Server 2008测试环境
--创建登陆名，并在给予其一些服务器角色权限，
--覆盖服务器固定角色、服务器对象等
USE master;
GO
IF EXISTS ( SELECT *
FROM sys.server_principals
WHERE name = 'Jack' )
DROP LOGIN [Jack];
CREATE LOGIN [Jack] WITH PASSWORD = 'dMkdj!d8@j'
,check_expiration=off;
GO
EXEC sp_addsrvrolemember @loginame = 'Jack'
, @rolename = 'securityadmin';
EXEC sp_addsrvrolemember @loginame = 'Jack'
, @rolename = 'dbcreator';
GO
GRANT IMPERSONATE ON LOGIN::[sa] TO [Jack];
GRANT CONTROL SERVER TO [Jack];
GRANT ALTER ON ENDPOINT::[TSQL Default TCP] TO [Jack];
GRANT ALTER ANY LOGIN TO [Jack] WITH GRANT OPTION;
GRANT VIEW DEFINITION ON LOGIN::[sa] TO [Jack];测试环境搭建完成后，我们执行如下测试脚本：
DECLARE @sql VARCHAR(max)
EXEC sp_DBA_LoginClone [Jack],@sql OUTPUT
SELECT @sql FOR XML PATH('')点击结果：
对比测试环境和测试结果，发现多了一条GRANT CONNECT SQL TO [Jack]。 因为在我们创建账户的时候，会默认给以账户连接实例的权限。将生成的脚本拷贝到其他SQL Server 2008 实例，可以创建Jack账户，并赋予指定的权限，使用测试环境的密码，可以登录新实例。测试OK！
我们再创建SQL Server 2016 测试环境继续测试：
--创建SQL Server 2016 测试环境
--创建登陆名，并在给予其一些服务器角色权限，
--覆盖服务器固定角色、服务器对象等
USE master;
GO
IF EXISTS ( SELECT *
FROM sys.server_principals
WHERE name = 'Jack' )
DROP LOGIN [Jack];
CREATE LOGIN [Jack] WITH PASSWORD = 'dMkdj!d8@j'
,check_expiration=off;
GO
EXEC sp_addsrvrolemember @loginame = 'Jack'
, @rolename = 'securityadmin';
EXEC sp_addsrvrolemember @loginame = 'Jack'
, @rolename = 'dbcreator';
GO
GRANT IMPERSONATE ON LOGIN::[sa] TO [Jack];
GRANT CONTROL SERVER TO [Jack];
GRANT ALTER ON ENDPOINT::[TSQL Default TCP] TO [Jack];
GRANT ALTER ANY LOGIN TO [Jack] WITH GRANT OPTION;
GRANT VIEW DEFINITION ON LOGIN::[sa] TO [Jack];
--适用SQL Server 2012及以后版本
GRANT ALTER ANY SERVER ROLE TO [Jack];
CREATE SERVER ROLE srvRoleTest
GRANT ALTER ANY AVAILABILITY GROUP to srvRoleTest
GRANT ALTER ANY SERVER ROLE to srvRoleTest
EXEC sp_addsrvrolemember Jack, srvRoleTest再次执行测试脚本，结果如下：
对比测试环境和测试结果，发现权限一致，同时在其他SQL Server 2016上可以创建Jack 账户，并且在新实例上Jack账户拥有和原实例相同的权限，且我们使用测试环境Jack的密码，可以在新实例上登陆。测试OK。
细心的朋友可能会发现，SQL Server 2008 生成的创建Jack账户的密码长度明显比SQL Server 2016中的长度短。这也是为什么高版本的账户不能克隆到低版本的原因所在。
最后将给出实例上所有账户克隆脚本，如下：
DECLARE @loginName sysname
,@loginsCloneSQL VARCHAR(max)
,@loginCloneSQL VARCHAR(max)
SET @loginsCloneSQL=''
DECLARE cur CURSOR FOR(
select name FROM sys.server_principals
Where type IN ( 'S', 'G', 'U' ) AND name <> 'sa'
)
OPEN cur
FETCH NEXT FROM cur INTO @loginName
WHILE @@FETCH_STATUS>=0
BEGIN
EXEC sp_DBA_LoginClone @loginName,@loginCloneSQL OUTPUT
SET @loginsCloneSQL=@loginsCloneSQL+@loginCloneSQL
FETCH NEXT FROM cur INTO @loginName
END
SELECT @loginsCloneSQL FOR XML PATH('')
CLOSE cur
DEALLOCATE cur
DECLARE @sql VARCHAR(max)
EXEC sp_DBA_LoginClone [Jack],@sql OUTPUT
SELECT @sql FOR XML PATH('')当然，你可以控制哪些账户需要克隆，仅仅需要改变游标中的查询条件即可。
如果想更为及时的获取最新文章，可以搜索注公众 MSQLServer，将有更多精彩。
297
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
