server level trigger - 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)

server level trigger

Author

Topic

Peter99
Constraint Violating Yak Guru

498 Posts

Posted - 2011-12-06 : 12:34:05

Hi,
I created server level trigger on my test machine just for testing purpose.
I was in user database:

use abc
go
create trigger xyz
on all server
for logon
as
begin
insert into audit_table values(suser_name())
end

Now I cannot login to server. There is message 'login failed for used aaaaaa.... due to trigger'

I want to delete this trigger but I cannot login, any help?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-06 : 13:27:23

Do you have the sa password or have another account that has sysadmin privileges?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Peter99
Constraint Violating Yak Guru

498 Posts

Posted - 2011-12-06 : 14:51:36

sa account was disabled.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-06 : 15:20:46

Well you'll need to find an account that has sysadmin access. Otherwise, you'll need to reinstall SQL and restore your databases.

How about a local admin account? By default, BUILTIN\Administrators have sysadmin. I hope you didn't disable the sa account AND removed this default access for local administrators. Or if you did both, then I hope you created an additional sysadmin account. You've got to have a way to get into the system if you mess things up like this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-12-06 : 15:27:50

If you have a sysadmin login, you can log in via the DAC, that doesn't fire login triggers.

--
Gail Shaw
SQL Server MVP

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-06 : 15:28:21

Ooohhh! Learned something new today. Cool.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Peter99
Constraint Violating Yak Guru

498 Posts

Posted - 2011-12-06 : 15:36:47

Thanks Tara and Gail, I will try.

Peter99
Constraint Violating Yak Guru

498 Posts

Posted - 2011-12-09 : 09:47:20

To resolve this issue: I had backup of system databases. I restored latest backup of master with different name, stop sql services, copy .mdf and .ldf files to sql server instance location which was not starting, started sql server and problem solved.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-09 : 12:58:29

Why did you do all of that? Connecting with the DAC and removing the trigger should have worked.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources