今天太忙(下班时,发现一个考试网站的不算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