46 lines
1.7 KiB
Transact-SQL
46 lines
1.7 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS [dev].[uspCreateSchema]
|
|
GO
|
|
|
|
CREATE PROCEDURE [dev].[uspCreateSchema]
|
|
@pIntezmenyAzonosito NVARCHAR(30),
|
|
@pSchemaUserPassword NVARCHAR(50)
|
|
AS
|
|
BEGIN
|
|
|
|
DECLARE @schema NVARCHAR(50) = 'KR_' + @pIntezmenyAzonosito +'_Schema'
|
|
DECLARE @schemaUser NVARCHAR(50) = 'KR_' + @pIntezmenyAzonosito + '_user'
|
|
DECLARE @sql NVARCHAR(MAX)
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = @schema) BEGIN
|
|
SET @sql = N'CREATE SCHEMA [' + @schema + '] AUTHORIZATION [dbo]'
|
|
EXEC sp_executesql @sql
|
|
END
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.sql_logins sl WHERE name = @schemaUser) BEGIN
|
|
SET @sql = N'CREATE LOGIN [' + @schemaUser + '] WITH PASSWORD = N''' + @pSchemaUserPassword + ''', DEFAULT_LANGUAGE = British'
|
|
EXEC sp_executesql @sql
|
|
END
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = @schemaUser) BEGIN
|
|
SET @sql = N'CREATE USER [' + @schemaUser + '] FOR LOGIN [' + @schemaUser + '] WITH DEFAULT_SCHEMA = [' + @schema + ']'
|
|
EXEC sp_executesql @sql
|
|
|
|
SET @sql = N'
|
|
GRANT SELECT ON SCHEMA::[' + @schema + '] TO [' + @schemaUser + ']
|
|
GRANT DELETE ON SCHEMA::[' + @schema + '] TO [' + @schemaUser + ']
|
|
GRANT UPDATE ON SCHEMA::[' + @schema + '] TO [' + @schemaUser + ']
|
|
GRANT INSERT ON SCHEMA::[' + @schema + '] TO [' + @schemaUser + ']
|
|
GRANT EXECUTE ON SCHEMA::[' + @schema + '] TO [' + @schemaUser + ']
|
|
GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO [' + @schemaUser + ']'
|
|
|
|
EXEC sp_executesql @sql
|
|
|
|
SET @sql = N'GRANT CONNECT TO [' + @schemaUser + ']'
|
|
EXEC sp_executesql @sql
|
|
END
|
|
ELSE BEGIN
|
|
SET @sql = N'ALTER USER [' + @schemaUser + '] WITH LOGIN = [' + @schemaUser + '] '
|
|
EXEC sp_executesql @sql
|
|
END
|
|
|
|
END
|