165 lines
4.7 KiB
Transact-SQL
165 lines
4.7 KiB
Transact-SQL
IF NOT EXISTS (SELECT 1 FROM master.dbo.syslogins WHERE name = 'kreta_tech_user' AND dbname = 'master')
|
|
BEGIN
|
|
USE [master]
|
|
CREATE LOGIN [kreta_tech_user] WITH PASSWORD=N'Porcica1.', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
|
|
GRANT CREATE ANY DATABASE TO [kreta_tech_user]
|
|
GRANT ALTER ANY LOGIN TO [kreta_tech_user]
|
|
END
|
|
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM master.dbo.syslogins WHERE name = 'kreta_db_user' AND dbname = 'master')
|
|
BEGIN
|
|
USE [master]
|
|
CREATE LOGIN [kreta_db_user] WITH PASSWORD=N'Porcica1.', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
|
|
END
|
|
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'kreta_tech_user')
|
|
BEGIN
|
|
CREATE USER [kreta_tech_user] FOR LOGIN [kreta_tech_user]
|
|
ALTER ROLE [db_owner] ADD MEMBER [kreta_tech_user]
|
|
GRANT BACKUP DATABASE TO [kreta_tech_user]
|
|
GRANT CREATE SCHEMA TO [kreta_tech_user]
|
|
GRANT EXECUTE TO [kreta_tech_user]
|
|
END
|
|
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'kreta_db_user')
|
|
BEGIN
|
|
CREATE USER [kreta_db_user] FOR LOGIN [kreta_db_user]
|
|
ALTER ROLE [db_datareader] ADD MEMBER [kreta_db_user]
|
|
ALTER ROLE [db_datawriter] ADD MEMBER [kreta_db_user]
|
|
GRANT CREATE SCHEMA TO [kreta_db_user]
|
|
GRANT EXECUTE TO [kreta_db_user]
|
|
END
|
|
|
|
GO
|
|
|
|
IF EXISTS (SELECT 1 FROM sys.services WHERE name = 'AuditServiceTarget') BEGIN
|
|
DROP SERVICE AuditServiceTarget
|
|
END
|
|
GO
|
|
|
|
IF EXISTS (SELECT 1 FROM sys.services WHERE name = 'AuditServiceInit') BEGIN
|
|
DROP SERVICE AuditServiceInit
|
|
END
|
|
GO
|
|
|
|
IF EXISTS (SELECT 1 FROM sys.services WHERE name = 'AuditService') BEGIN
|
|
DROP SERVICE [AuditService]
|
|
END
|
|
GO
|
|
|
|
IF EXISTS (SELECT 1 FROM sys.service_queues WHERE name = 'AuditQueue') BEGIN
|
|
DROP QUEUE auditlog.AuditQueue
|
|
END
|
|
GO
|
|
|
|
IF EXISTS (SELECT 1 FROM sys.service_contracts WHERE name = 'AuditContract') BEGIN
|
|
DROP CONTRACT AuditContract
|
|
END
|
|
GO
|
|
|
|
IF EXISTS (SELECT 1 FROM sys.service_message_types WHERE name = 'AuditMessage') BEGIN
|
|
DROP MESSAGE TYPE AuditMessage
|
|
END
|
|
GO
|
|
|
|
DECLARE @sql nvarchar(max)
|
|
|
|
EXEC sp_changedbowner 'sa';
|
|
|
|
IF (SELECT is_broker_enabled FROM sys.databases WHERE database_id = DB_ID()) = 0 BEGIN
|
|
SET @sql = N'ALTER DATABASE ' + DB_NAME() + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE '
|
|
EXEC master.sys.sp_executesql @sql
|
|
|
|
-- Ha a Service Broker IDja nem egyedi
|
|
IF EXISTS (SELECT 1 FROM sys.databases x WHERE x.service_broker_guid = (SELECT service_broker_guid FROM sys.databases d WHERE d.database_id = DB_ID()) AND x.database_id <> DB_ID()) BEGIN
|
|
SET @sql = N'ALTER DATABASE ' + DB_NAME() + ' SET NEW_BROKER'
|
|
EXEC master.sys.sp_executesql @sql
|
|
END
|
|
ELSE BEGIN
|
|
SET @sql = N'ALTER DATABASE ' + DB_NAME() + ' SET ENABLE_BROKER'
|
|
EXEC master.sys.sp_executesql @sql
|
|
END
|
|
|
|
SET @sql = N'ALTER DATABASE ' + DB_NAME() + ' SET MULTI_USER'
|
|
EXEC master.sys.sp_executesql @sql
|
|
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.service_message_types WHERE name = 'AuditMessage') BEGIN
|
|
CREATE MESSAGE TYPE [AuditMessage]
|
|
VALIDATION = NONE;
|
|
END
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.service_message_types WHERE name = 'EndOfStream') BEGIN
|
|
CREATE MESSAGE TYPE EndOfStream;
|
|
END
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.service_contracts WHERE name = 'AuditContract') BEGIN
|
|
CREATE CONTRACT [AuditContract] (
|
|
[AuditMessage] SENT BY INITIATOR,
|
|
[EndOfStream] SENT BY INITIATOR
|
|
);
|
|
END
|
|
GO
|
|
|
|
IF OBJECT_ID('auditlog.usp_AuditProcessing') IS NULL BEGIN
|
|
EXEC('CREATE PROCEDURE auditlog.usp_AuditProcessing AS SELECT 1')
|
|
END
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.service_queues WHERE name = 'AuditQueue') BEGIN
|
|
CREATE QUEUE auditlog.AuditQueue WITH STATUS=ON, ACTIVATION (
|
|
STATUS = ON
|
|
,MAX_QUEUE_READERS = 1
|
|
,PROCEDURE_NAME = auditlog.usp_AuditProcessing
|
|
,EXECUTE AS 'kreta_tech_user'
|
|
);
|
|
END
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.services WHERE name = 'AuditService') BEGIN
|
|
CREATE SERVICE [AuditService]
|
|
ON QUEUE auditlog.AuditQueue ([AuditContract]);
|
|
END
|
|
GO
|
|
|
|
IF OBJECT_ID('auditlog.ErrorLog') IS NOT NULL
|
|
DROP TABLE [auditlog].[ErrorLog];
|
|
GO
|
|
|
|
CREATE TABLE [auditlog].[ErrorLog](
|
|
[ErrorLogID] [int] IDENTITY(1,1) NOT NULL,
|
|
[ErrorTime] [datetime] NOT NULL,
|
|
[UserName] [sysname] NOT NULL,
|
|
[ErrorNumber] [int] NOT NULL,
|
|
[ErrorSeverity] [int] NULL,
|
|
[ErrorState] [int] NULL,
|
|
[ErrorProcedure] [nvarchar](126) NULL,
|
|
[ErrorLine] [int] NULL,
|
|
[ErrorMessage] [nvarchar](4000) NOT NULL,
|
|
CONSTRAINT [PK_ErrorLog_ErrorLogID] PRIMARY KEY CLUSTERED ([ErrorLogID] ASC)
|
|
)
|
|
GO
|
|
|
|
ALTER TABLE [auditlog].[ErrorLog]
|
|
ADD CONSTRAINT [DF_ErrorLog_ErrorTime]
|
|
DEFAULT (getdate()) FOR [ErrorTime]
|
|
GO
|
|
|
|
ALTER QUEUE auditLog.[AuditQueue]
|
|
WITH ACTIVATION
|
|
(
|
|
STATUS = ON,
|
|
PROCEDURE_NAME = auditLog.usp_AuditProcessing,
|
|
MAX_QUEUE_READERS = 1,
|
|
EXECUTE AS 'kreta_tech_user'
|
|
);
|
|
GO
|