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

admin

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

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

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

脚本如下：

[mw_shl_code=sql,true]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[/mw_shl_code]