lankapala
asked on
sql server major issue need help
I have wrote following trigger after that Sql server not working how to resolve this
I think , this the issue "ROLLBACK; " how to remove the Trigger. so i can't connect the SQL server using Management console.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [DDL_AUDIT_Logins] ON ALL SERVER
FOR ADD_SERVER_ROLE_MEMBER
,DDL_GDR_SERVER_EVENTS
,DROP_SERVER_ROLE_MEMBER
,DDL_LOGIN_EVENTS, DDL_AUTHORIZATION_SERVER_E VENTS, ADD_ROLE_MEMBER, DROP_ROLE_MEMBER
,LOGON
AS
SET NOCOUNT ON;
DECLARE @EventsTable TABLE (
EType NVARCHAR(max)
,EObject VARCHAR(100)
,EDate DATETIME
,EUser VARCHAR(100)
,ECommand NVARCHAR(max)
);
DECLARE @EType NVARCHAR(max);
DECLARE @ESchema NVARCHAR(max);
DECLARE @DBName VARCHAR(100);
DECLARE @Subject VARCHAR(200);
DECLARE @EObject VARCHAR(100);
DECLARE @EObjectType VARCHAR(100);
DECLARE @EMessage NVARCHAR(max);
DECLARE @ETSQL NVARCHAR(max);
SELECT @EType = EVENTDATA().value('(/EVENT _INSTANCE/ EventType) [1]',
'nvarchar(max)')
,@ESchema = EVENTDATA().value('(/EVENT _INSTANCE/ SchemaName )[1]',
'nvarchar(max)')
,@EObject = EVENTDATA().value('(/EVENT _INSTANCE/ ObjectName )[1]',
'nvarchar(max)')
,@EObjectType = EVENTDATA().value('(/EVENT _INSTANCE/ ObjectType )[1]',
'nvarchar(max)')
,@DBName = EVENTDATA().value('
(/EVENT_INSTANCE/DatabaseN ame)[1]',
'nvarchar(max)')
,@ETSQL = EVENTDATA().value('(/EVENT _INSTANCE/ TSQLComman d/CommandT ext)[1]',
'nvarchar(max)');
INSERT INTO @EventsTable
SELECT @EType
,@EObject
,GETDATE()
,SUSER_SNAME()
,@ETSQL;
SET @EMessage = 'Login_Event: ' + @EType + CHAR(10) + 'Event Occured at: '
+ Convert(VARCHAR, GETDATE()) + CHAR(10) + 'Changed Login: ' + @EObject +
CHAR(10) + 'Changed by: ' + SUSER_SNAME() + CHAR(10) + 'Executed T-SQL: ' +
@ETSQL
ROLLBACK;
SET NOCOUNT OFF;
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ENABLE TRIGGER [DDL_AUDIT_Logins] ON ALL SERVER
GO
I think , this the issue "ROLLBACK; " how to remove the Trigger. so i can't connect the SQL server using Management console.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [DDL_AUDIT_Logins] ON ALL SERVER
FOR ADD_SERVER_ROLE_MEMBER
,DDL_GDR_SERVER_EVENTS
,DROP_SERVER_ROLE_MEMBER
,DDL_LOGIN_EVENTS, DDL_AUTHORIZATION_SERVER_E
,LOGON
AS
SET NOCOUNT ON;
DECLARE @EventsTable TABLE (
EType NVARCHAR(max)
,EObject VARCHAR(100)
,EDate DATETIME
,EUser VARCHAR(100)
,ECommand NVARCHAR(max)
);
DECLARE @EType NVARCHAR(max);
DECLARE @ESchema NVARCHAR(max);
DECLARE @DBName VARCHAR(100);
DECLARE @Subject VARCHAR(200);
DECLARE @EObject VARCHAR(100);
DECLARE @EObjectType VARCHAR(100);
DECLARE @EMessage NVARCHAR(max);
DECLARE @ETSQL NVARCHAR(max);
SELECT @EType = EVENTDATA().value('(/EVENT
'nvarchar(max)')
,@ESchema = EVENTDATA().value('(/EVENT
'nvarchar(max)')
,@EObject = EVENTDATA().value('(/EVENT
'nvarchar(max)')
,@EObjectType = EVENTDATA().value('(/EVENT
'nvarchar(max)')
,@DBName = EVENTDATA().value('
(/EVENT_INSTANCE/DatabaseN
'nvarchar(max)')
,@ETSQL = EVENTDATA().value('(/EVENT
'nvarchar(max)');
INSERT INTO @EventsTable
SELECT @EType
,@EObject
,GETDATE()
,SUSER_SNAME()
,@ETSQL;
SET @EMessage = 'Login_Event: ' + @EType + CHAR(10) + 'Event Occured at: '
+ Convert(VARCHAR, GETDATE()) + CHAR(10) + 'Changed Login: ' + @EObject +
CHAR(10) + 'Changed by: ' + SUSER_SNAME() + CHAR(10) + 'Executed T-SQL: ' +
@ETSQL
ROLLBACK;
SET NOCOUNT OFF;
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ENABLE TRIGGER [DDL_AUDIT_Logins] ON ALL SERVER
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
??? is this a google translate?