Auditdb - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Auditdb

Author

Topic

reading01
Starting Member

2 Posts

Posted - 2009-07-27 : 15:45:53

Hello,

Ran the below script against my server. Now I can not log back into SQL management studio. I tried logging in with admin: but no luck. Does anyone know how I can drop all triggers the below script put in place?

/* Create Audit Database */
CREATE DATABASE AuditDb
GO
USE AuditDb
GO
/* Create Audit Table */
CREATE TABLE ServerLogonHistory
(SystemUser VARCHAR(512),
DBUser VARCHAR(512),
SPID INT,
LogonTime DATETIME)
GO
/* Create Logon Trigger */
CREATE TRIGGER Tr_ServerLogon
ON ALL SERVER FOR LOGON
AS
BEGIN
INSERT INTO AuditDb.dbo.ServerLogonHistory
SELECT SYSTEM_USER,USER,@@SPID,GETDATE()
END
GO
/* Dropping Database AuditDB */
/* Please note login to Server again will
Produce Errors */
USE master
GO
DROP DATABASE AuditDB
GO

afernandez
Starting Member

1 Post

Posted - 2009-07-27 : 17:10:54

Try the following:

Connect to SQL using the DAC via sqlcmd (make sure SQL Browser is started)

SQLCMD.exe -S<instance_name> -A

once logged on, issue the following statement:

drop trigger tr_serverlogon on all server;
go

That *should* do it. I just tested it on my workstation.

TimBenninghoff
Starting Member

1 Post

Posted - 2009-07-27 : 18:14:51

I had the same experience with admin: via SSMS. I was able to get in to my server to disable the trigger by using sqlcmd -A while I was RDP'd to the server. I had to remote into the server because I leave remote DAC disabled.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources