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