Link to home
Start Free TrialLog in
Avatar of lankapala
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_EVENTS, 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/DatabaseName)[1]',
 'nvarchar(max)')
,@ETSQL = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[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
Avatar of HainKurt
HainKurt
Flag of Canada image

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.

??? is this a google translate?
ASKER CERTIFIED SOLUTION
Avatar of Nakul Vachhrajani
Nakul Vachhrajani
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial