[Source] sql2012 includes a database to quickly generate user tsql scripts

Today is too busy (when I got off work, I found a bug on an exam website that was not a bug, which just allowed me to find the data I wanted, and now I am working hard to code... ）

Not much to say, today's technical article, to put it simply, is a piece of SQL code written yesterday

Used to generate a CREATE script that contains the level 2 accounts in the database in the SQL2012

The script is as follows:

[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='
--Write an account number
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
--Read the account
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 '--write account number uid:'+@dbname+'_cw pwd:'+@password
print '--read account number uid:'+@dbname+'_cr pwd:'+@password2
go[/mw_shl_code]