sql2012包含数据库，快速生成用户tsql脚本

今天太忙（下班时，发现一个考试网站的不算BUG的BUG，这个BUG刚好能让我找到想要的数据，现在正辛苦的编码中...）

不多说，今天的技术文章，简单一点，帖一段昨天写的SQL代码

用于SQL2012中包含数据库中 2级帐号的CREATE生成脚本

 

脚本如下：                

 

USE [master]
GO
/*
exec usp_addcontaindbuser 'dba_TEST51ak'
*/
create proc usp_addcontaindbuser(
@dbname varchar(50)
)
as
set nocount on 

declare @password varchar(50)
declare @password2 varchar(50)
declare @sqlstr varchar(max)


set @password=lower(left(newid(),8))
set @password2=lower(left(newid(),8))

set @sqlstr='
--写帐号
USE ['+@dbname+']
GO
CREATE USER ['+@dbname+'_cw] WITH PASSWORD=N'''+@password+''', DEFAULT_SCHEMA=[dbo]
GO
USE ['+@dbname+']
GO
ALTER ROLE [db_datawriter] ADD MEMBER ['+@dbname+'_cw]
GO
USE ['+@dbname+']
GO
ALTER ROLE [db_exec] ADD MEMBER ['+@dbname+'_cw]
GO

--读帐号
USE ['+@dbname+']
GO
CREATE USER ['+@dbname+'_cr] WITH PASSWORD=N'''+@password2+''', DEFAULT_SCHEMA=[dbo]
GO
USE ['+@dbname+']
GO
ALTER ROLE [db_datareader] ADD MEMBER ['+@dbname+'_cr]
GO
USE ['+@dbname+']
GO
ALTER ROLE [db_exec] ADD MEMBER ['+@dbname+'_cr]
GO

'
print (@sqlstr)
print '--写帐号  uid:'+@dbname+'_cw  pwd:'+@password
print '--读帐号  uid:'+@dbname+'_cr  pwd:'+@password2

go

 

　　

 

转载于:https://www.cnblogs.com/wokofo/p/3199993.html