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

本文主要是介绍sqlserver 登录名和账户复制一套代码，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

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

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

use master;
go
create view v_random as select newid() as MacoId; goCREATE 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
);GOcreate 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,rolenameOPEN 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 
ENDCLOSE 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')

 

这篇关于sqlserver 登录名和账户复制一套代码的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---