sqlserver 登录名和账户复制一套代码

先要执行准备签的临时表和自定义随机值函数的创建

直接建在master下,数据库就都能使用了，但一定要有高权限才行

use master;
go
create view v_random as select newid() as MacoId; 

go

CREATE TABLE privs_test
(
Ownername varchar (300) COLLATE Chinese_PRC_CI_AS NULL,
tablename varchar (300) COLLATE Chinese_PRC_CI_AS NULL,
usernm varchar (300) COLLATE Chinese_PRC_CI_AS NULL,
grantor varchar (300) COLLATE Chinese_PRC_CI_AS NULL,
typename varchar (300) COLLATE Chinese_PRC_CI_AS NULL,
actionname varchar (300) COLLATE Chinese_PRC_CI_AS NULL,
clu varchar (300) COLLATE Chinese_PRC_CI_AS NULL
);

GO

create function [dbo].[m_rand](@mycount int)  
returns nvarchar(2000)  
as  
begin  
       declare @maco_wang table (id varchar(1))  
       declare @maco_number int,@number int;  
       declare @my_one nvarchar(max),@my_two nvarchar(max)  
       set @my_one='';set @maco_number=0; set @number =48;  
       while (@number>=48 and @number<=57) or (@number>=65 and @number<=90) or (@number>=97 and @number<=122)   
       begin  
           insert into @maco_wang select char(@number)  
           set @number=@number+1;  
           if(@number=58)  
           begin  
              set @number=65            
           end  
           if(@number=91)  
           begin       
              set @number=97     
           end  
       end  
       while @maco_number<@mycount  
       begin  
              select @my_two=id from @maco_wang  
              order by (select MacoId from dbo.v_random);  
              set @my_one=@my_two+@my_one;  
              set @maco_number=@maco_number+1;  
       end  
    return @my_one  
end;

创建XML大型语句，执行即可

--40
DECLARE
@Database varchar(555),
@sq4 nvarchar(max);

--初始化
SET @sq4=N''

DECLARE c_database CURSOR LOCAL  FOR SELECT name FROM  sysdatabases WHERE name  IN (需要循环的数据库) 
OPEN  c_database
FETCH NEXT FROM c_database INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sq4=@sq4+N'
DECLARE
--用于用户
@loginName varchar(555),
@roleName varchar(555),
@sql nvarchar(max),
@sq2 nvarchar(max),
@newpasswd varchar(50),
@oldpasswd varchar(50),
--设置新账号
@newLoginname VARCHAR(50),
@Database varchar(555),

--用于权限
@sq3 nvarchar(max),
@Owner varchar(100),
@tablename varchar(100),
@usernm varchar(100),
@grantor varchar(100),
@type varchar(100),
@actionname varchar(100),
@column varchar(100);


--设置数据库
USE '+@Database+'

SET @sql=N'''';
DECLARE curLogin CURSOR LOCAL for
select DB_NAME() as dbname,sp.name as username,dpr.name as rolename 
from sys.database_principals dp 
LEFT JOIN sys.database_role_members drm on drm.member_principal_id=dp.principal_id
LEFT JOIN sys.database_principals dpr on drm.role_principal_id=dpr.principal_id 
join sys.server_principals sp on sp.name=dp.name
where 1=1
--and dpr.is_fixed_role=1 
and sp.type =''S'' --SQL USER,WINDOWS USER AND windows group
AND sp.is_disabled =0
order by username,rolename


OPEN curLogin;

FETCH NEXT FROM curLogin INTO @Database,@loginName,@roleName;

WHILE @@FETCH_STATUS = 0
BEGIN
set @newpasswd=master.dbo.m_rand(30);
set @newLoginname=@LoginName+''2'';


--新增登录名和用户
if not exists(select * from sys.server_principals where name=@newLoginname) 
BEGIN
SET @sql=@sql+N''
use ''+@Database+'';
print ''''账号: ''+@newLoginname+'' 密码: ''+@newpasswd+'''''';
CREATE login ''+QUOTENAME(@newLoginname)+'' with password=''''''+@newpasswd+''''''; 
CREATE USER ''+QUOTENAME(@newLoginname)+'' for login ''+QUOTENAME(@newLoginname)+'' ; 
'';
END;
else 
if not exists(select * from sys.database_principals where name=@newLoginname) 
BEGIN
SET @sql=@sql+N''
use ''+@Database+'';
CREATE USER ''+QUOTENAME(@newLoginname)+'' for login ''+QUOTENAME(@newLoginname)+''; 
'';

end;
--处理角色权限
if @roleName IS NOT NULL
begin
select @sql=@sql+N''
exec sp_addrolemember ''+@roleName+'', '' + @newLoginname+''
''
END

--处理用户单表权限
SET @usernm=@LoginName
set @sq3=''insert into master.dbo.privs_test exec sp_helprotect @username = ''+@usernm+'''';
EXEC sp_executesql @sq3
DECLARE tableprivs CURSOR LOCAL FOR select Ownername,tablename,usernm,grantor,typename,actionname,clu from master.dbo.privs_test WHERE Ownername not in (''.'') AND tablename not in (''.'')
OPEN tableprivs;

FETCH NEXT FROM tableprivs INTO @Owner,@tablename,@usernm,@grantor,@type,@actionname,@column;
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql=@sql+N''
grant ''+@actionname+'' on ''+@tablename+'' to ''+@newLoginname+'';
''
FETCH NEXT FROM tableprivs INTO @Owner,@tablename,@usernm,@grantor,@type,@actionname,@column;
END
CLOSE tableprivs
DEALLOCATE tableprivs;


--修改原登录名密码
--SET @sql=@sql+N''ALTER LOGIN ''+@LoginName+'' with passward= ''+@oldpasswd+'' '';

--继续循环游标
FETCH NEXT FROM curLogin INTO @Database,@loginName,@roleName;
truncate TABLE master.dbo.privs_test 
END

CLOSE curLogin
DEALLOCATE curLogin;


SELECT @sql FOR XML PATH(''test'')
exec sp_executesql @sql
GO
'
FETCH NEXT FROM c_database INTO @Database
end
CLOSE c_database
DEALLOCATE c_database
SELECT @sq4 FOR XML PATH('test1')