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