《克隆 SQL Server 登陆名及权限》一文中对登陆名的克隆相对适用范围较窄,为更好的应用,对脚本进行了扩充和完善,具体如sp_DBA_LoginPermissionsClone。
sp_DBA_LoginPermissionsClone有两个用途,区别主要在于第二、三个参数。@newLoginName和@loginName相同或者为NULL时,进行的是登陆账户权限克隆(生成的脚本账户名、权限和密码与原先登陆账户相同);@newLoginName非NULL且不等于@loginName时,@pwd 非NULL,则生成将原账户权限(@loginNam)克隆给新账户(@newLoginName)的脚本(账户名、密码为新的,权限和原账户相同)。
如下为sp_DBA_LoginPermissionsClone具体脚本:
USE [master]
GO
/*
功能:生成克隆帐户权限的脚本或者将一个登陆帐户的权限赋予另外一个登陆帐户的脚本
适用于SQL SERVER 2008/SQL SERVER 2008 R2/SQL SERVER 2012/SQL SERVER 2014/SQL SERVER 2016
***其他版本暂未测试
注意: 1.适用于将一个登陆名的权限赋予另外一个登陆名
2.不能克隆凭据、非对称密钥和证书
3.如果一个登陆名有管理另外登陆名的权限,且所管理的登陆名也是新的,需要手动调整对应的脚本
实例:
1. 生成克隆帐户权限的脚本
DECLARE @sql VARCHAR(max)
EXEC sp_DBA_LoginPermissionsClone [Jack],NULL,NULL,@sql OUTPUT
SELECT @sql FOR XML PATH('')
2. 将一个帐户的权限赋予一个新的帐户
DECLARE @sql VARCHAR(max)
EXEC sp_DBA_LoginPermissionsClone [Jack],[Jack_new],'password',@sql OUTPUT
SELECT @sql FOR XML PATH('')
*/
CREATE PROC [dbo].[sp_DBA_LoginPermissionsClone]
@loginName sysname
,@newLoginName sysname
,@pwd VARCHAR(25)=NULL
,@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
) OR @loginName IS NULL
BEGIN
PRINT @loginName + ' 不存在'
RETURN
END
IF EXISTS (
SELECT 1 FROM sys.syslogins WHERE name= @newLoginName
) AND @newLoginName<>@loginName
BEGIN
PRINT @newLoginName + ' 已存在'
RETURN
END
IF @newLoginName<>@loginName AND @pwd IS NULL AND @newLoginName IS NOT NULL
BEGIN
PRINT '请输入密码'
RETURN
END
IF @newLoginName IS NULL
SET @newLoginName=@loginName
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 @newLoginName<>@loginName
BEGIN
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( @newLoginName,']' )
+ ' 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(@newLoginName, ']')+CHAR(10)
+' WITH PASSWORD='''+@pwd+'''' +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
END
ELSE IF @loginName=@newLoginName
BEGIN
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
--PRINT '---------------'
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
END
--PRINT @loginCloneSQL
--IF (@denylogin = 1)
--BEGIN --login is denied access
-- SET @loginCloneSQL = @loginCloneSQL + ' DENY CONNECT SQL TO ' + QUOTENAME( @newLoginName )+CHAR(10)
--END
--ELSE IF (@hasaccess = 0)
--BEGIN --login exists but does not have access
-- SET @loginCloneSQL = @loginCloneSQL + ' REVOKE CONNECT SQL TO ' + QUOTENAME( @newLoginName )+CHAR(10)
--END
IF (@is_disabled = 1)
BEGIN --login is disabled
SET @loginCloneSQL = @loginCloneSQL + ' Alter LOGIN ' + QUOTENAME( @newLoginName ) + '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(@newLoginName, '[') + CHAR(10)
WHEN ISNULL(ISNULL(o.name, e.name), ag.name) IS NULL
AND sp.state = 'W'
THEN 'GRANT ' + sp.permission_name + ' TO ' + QUOTENAME(@newLoginName, '[')
+ ' ' + ' 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(@newLoginName, '[') + 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(@newLoginName, '[') + ' ' + ' 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(@newLoginName, '[') + ' 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(@newLoginName, '[') + ' '
+ ' 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(@newLoginName, '[')
+ 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(@newLoginName, '[') + ' '
+ ' 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(@newLoginName, '[') + CHAR(10)
WHEN ISNULL(o.name, e.name) IS NULL
AND sp.state = 'W'
THEN 'GRANT ' + sp.permission_name + ' TO ' + QUOTENAME(@newLoginName, '[')
+ ' 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(@newLoginName, '[') + 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(@newLoginName, '[') + ' ' + ' 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(@newLoginName, '[')
+ 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(@newLoginName, '[') + ' '
+ ' 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(@newLoginName,'[') + ',' + 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
GO
EXECUTE sys.sp_MS_marksystemobject 'sp_DBA_LoginPermissionsClone';
GO
如果想更为及时的获取最新文章,可以搜索注公众 MSQLServer,将有更多精彩。