创建DB

这是一个融合：创建规范目录，DB，login,user,添加角色

use master
GO

DECLARE @db sysname
		,@flag_run INT
		,@login SYSNAME
		,@prd NVARCHAR(50)
;

SELECT @db = 'DBName'
		,@login = 'LoginName'
		,@prd = N'PWD'
		,@flag_run = 1

IF NOT EXISTS(SELECT TOP 1 *
			FROM sys.sysdatabases WITH (NOLOCK)
			WHERE name=@db)
BEGIN

/************************Make directory of TempTable file**************************/
	--Make directory of TempTable file
	DECLARE @SQL_path NVARCHAR(520)
			,@Log_path NVARCHAR(520)
			,@sql NVARCHAR(max)

	SELECT TOP 1 @SQL_path=left(physical_name,3)+'DATA\'+CASE @@SERVICENAME
							WHEN 'MSSQLSERVER' THEN @@SERVERNAME
							ELSE @@SERVICENAME
						END+'\' + @db + '\SQL\'
				,@Log_path=left(physical_name,3)+'DATA\'+CASE @@SERVICENAME
							WHEN 'MSSQLSERVER' THEN @@SERVERNAME
							ELSE @@SERVICENAME
						END+'\' + @db + '\LOG\'
	FROM sys.master_files WITH (NOLOCK)
	WHERE db_name(database_id) not in ('master','tempdb','msdb'
									,'model','distribution')
		and physical_name like '%SQL%' 

SET @sql = '
	CREATE DATABASE ' + @db + ' 
	ON  PRIMARY 
	( NAME = N''' + @db + '_Data''
		, FILENAME = '''+@SQL_path+'' + @db + '_Data.mdf'+'''
		, FILEGROWTH = 100MB )
	 LOG ON 
	( NAME = N''' + @db + '_Log''
		, FILENAME = '''+@Log_path+'' + @db + '_Log.ldf'+'''
		, FILEGROWTH = 100MB 
		, MAXSIZE = 10GB )'
/************************Make directory of TempTable file**************************/

/************************Create database TempTable**************************/
IF @flag_run = 1
BEGIN
	--Create folder directory
	EXEC master.sys.xp_create_subdir @SQL_path
	EXEC master.sys.xp_create_subdir @Log_path
	
	exec(@sql)
END
ELSE
	PRINT @sql		

/************************Create database TempTable**************************/
END


IF NOT EXISTS(
SELECT TOp 1 *
FROM sys.syslogins
WHERE name = @login
)
BEGIN
	EXEC('CREATE LOGIN '+@login+' WITH PASSWORD = N'''+@prd+''',CHECK_policy = OFF')
END

--CREATE LOGIN LoginName WITH PASSWORD = 'pwd',CHECK_policy = OFF
--drop LOGIN LoginName 

EXEC('CREATE LOGIN '+@login+' WITH PASSWORD = N'''+@prd+''',CHECK_policy = OFF')

USE DBName
--更改当前DB所有者为sa,避免后续创建DB用户失效或删除侯造成问题
EXEC sp_changedbowner 'sa'

DECLARE @db sysname
		,@flag_run INT
		,@login SYSNAME
		,@prd NVARCHAR(50)
;

SELECT @db = 'DBName'
		,@login = 'LOGIN'
		,@prd = N'pwd'
		,@flag_run = 1

IF NOT EXISTS(
SELECT TOp 1 *
FROM sys.sysusers
WHERE name = @login
)
BEGIN
	EXEC('create user '+@login+' for login '+@login)
	EXEC('EXEC sp_addrolemember ''db_owner'','+@login)
	--EXEC('create user '+@login+' for login '+@login)
	--EXEC('EXEC sp_addrolemember ''db_datareader'','+@login)
END


GO
--use DBName;
--EXEC('create user userName for login LoginName ')
--EXEC('EXEC sp_addrolemember ''db_owner'',userName')