Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a legacy application installed on my server which has configured to work only with one user "sa" and there is time bomb like script written inside this application which whenever it finds another user it will delete that user without any hassle.

We need to have access to database with another application and password of "sa" is lost so the only way remaining is to create another user with sysadmin privileges.

but since the script deletes all the users I can't do anything special, so my question is :

How can I prevent that script or rollback its job every time it is trying to delete my user?

share|improve this question
1  
How can you add a user if you don't know the sa password, and all other accounts are automatically deleted? –  Max Vernon Jan 2 at 2:56
1  
read this @MaxVernon : v-consult.be/2011/05/26/… –  austin powers Jan 2 at 20:17
    
Ahhh yes, I keep forgetting about single user mode. –  Max Vernon Jan 2 at 21:32

1 Answer 1

up vote 5 down vote accepted
CREATE TRIGGER [DDL_PreventLoginDrop]ON ALL SERVER 
FOR DROP_LOGIN
AS 
DECLARE @LoginName NVARCHAR(128),@eventData XML

SET @eventData = EVENTDATA()            
SELECT @LoginName=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')
IF @LoginName IN ('Login name that you want to protect')
  BEGIN
    PRINT 'Gotha, you nasty application!' 
    ROLLBACK;
  END
GO
ENABLE TRIGGER [DDL_PreventLoginDrop] ON ALL SERVER 

disclaimer: Hopefully you'll agree that this answers your question "How to prevent dropping a login" but in no way is this a proper root cause fix. However, I know that there are some pretty nasty legacy applications where it's not possible to change the code... I must admit, this one just made me smile..

share|improve this answer
    
Thank you for your straight forward answer, I don't want to be rude but I need to wait 12 more hours to see if the legacy app tries to delete my new user, then I will accept your answer. –  austin powers Jan 2 at 20:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.