DEFINITIONS

Definitions More Info.
Definition ID2.019
TitleSQL
CategoryNOTES
DefinitionSSMS Management studio ile sys admin yetkisi engelleme
Definition DescriptionBazen apiler için sysadmin yetkisi verilmektedir ve bu api kullanicilarini developer arkadaslarin kullanmasini engellemek icin olusturulan bir triggerdir.

USE [master]
GO

/****** Object: DdlTrigger [logon_ddl_trg_Users] Script Date: 4/17/2020 3:28:31 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



--CREATE TRIGGER [logon_ddl_trg_Users]
--ON ALL SERVER --WITH EXECUTE AS 'sa'
--FOR LOGON
--AS
--BEGIN
-- IF ORIGINAL_LOGIN()= 'preprodtest'
-- BEGIN
-- rollback;
-- END
--END



CREATE TRIGGER [logon_ddl_trg_Users]
ON ALL SERVER --WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN() IN ( 'elastic', 'AppUser', 'AspUser', 'KNSPACE\elasticsearch', 'KNSPACE\ConsoleApp', 'KNSPACE\AppUser', 'KNSPACE\AspUser', 'KNSPACE\WebApi' )
AND APP_NAME()= 'Microsoft SQL Server Management Studio'
BEGIN
ROLLBACK

DECLARE @LogonTriggerData XML,
@EventTime DATETIME,
@LoginName VARCHAR(50),
@ClientHost VARCHAR(50),
@LoginType VARCHAR(50),
@HostName VARCHAR(50),
@AppName VARCHAR(500)

SET @LogonTriggerData = EVENTDATA()

SET @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
SET @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
SET @ClientHost = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
SET @HostName = HOST_NAME()
SET @AppName = APP_NAME()

INSERT INTO DPAudit.[dbo].[LogonAuditing]
(
SessionId,
LogonTime,
HostName,
ProgramName,
LoginName,
ClientHost
)
SELECT
@@spid,
GETDATE(),
@HostName,
@AppName,
ORIGINAL_LOGIN(),
@ClientHost

END
END

GO

ENABLE TRIGGER [logon_ddl_trg_Users] ON ALL SERVER
GO
RecordBycunay
Record Date17-04-2020 15:51:55
Düzenle
Kopyala
Sil