DDL trigger for RESTORE DATABSE

  • Hi,

    I have to create a user in each user database after a database has been created or restored in SQL Server 2005. Not able to do so with a DDL trigger for restore operation. Any solution?

    Regards,

    Ajay Prakash


    Kindest Regards,

    Ajay Prakash

  • unlike create database, restore is not a ddl statement with server scope; read BOL ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a3d3c1a5-6ca0-465b-b1d4-f197dd2b682d.htm

  • I do not understand the question. are you attenpting to create a database(or restore the db) and then create the user with a triggered?

    am correct?

    "We never plan to Fail, We just fail to plan":)

  • The new user has to be created after any of the below mentioned operation happens.

    1. If a new database has been created

    OR

    2. If a any database has been retored.


    Kindest Regards,

    Ajay Prakash

  • Presumably the database being restored is from another server? Otherwise your user would already exist if you were restoring a database that was already present on your server (assuming you have retroactively created this user on all your existing databases).



    Scott Duncan

    MARCUS. Why dost thou laugh? It fits not with this hour.
    TITUS. Why, I have not another tear to shed;
    --Titus Andronicus, William Shakespeare


  • Scott,

    I work in an environment where my team is supporting almost 500 server with all sorts of environment(DEV/UAT/SIT/PROD/DR). These systems are being used by multiple applications, which often requires database restoration from PROD no NON PROD or from NON PROD as well NON PROD. On the top of this application team may tell us to restore databases of 2 years back. I may rollover the users accross the servers once but this will not suffice my requirement. We want to track object creation in any user database and a report goes to each application manager/owner next day. EAch server is capable of sending report on its own.

    Therefore I was thinking to have a DDL trigger at each user database level and a server level DDL trigger to cater create and restore database.

    Even SQLAgent job does not address my concern accurately becaue the objects can be created just after a restore happnes which will not be rerported. We have a central repository on each server where all database reports it's object creation

    Regards,

    Ajay Prakash


    Kindest Regards,

    Ajay Prakash

  • In lieu of a trigger, have you considered scripting the restore process, including post-restore steps of creating the user you require? If it is a regular occurrence, maybe implement as a stored procedure, passing db name, path to data files, path to backup file.



    Scott Duncan

    MARCUS. Why dost thou laugh? It fits not with this hour.
    TITUS. Why, I have not another tear to shed;
    --Titus Andronicus, William Shakespeare


  • Thanks!! I think this may be a good solution to it. Will try not sure if that will be feasible for all to follow. But I will try my best..


    Kindest Regards,

    Ajay Prakash

  • See http://www.sqlservercentral.com/Forums/Topic948331-149-1.aspx for a full solution including auditing of RESTORE DATABASE and CREATE DATABASE FOR ATTACH in SQL 2005 (and SQL 2008)

  • SecretSQuireL (7/7/2010)


    See http://www.sqlservercentral.com/Forums/Topic948331-149-1.aspx for a full solution including auditing of RESTORE DATABASE and CREATE DATABASE FOR ATTACH in SQL 2005 (and SQL 2008)

    It seems that this forum entry does not exist. Or is it the wrong url?

    Regards

    Dirk

    --
    May you never suffer the sentiment of spending a day without any purpose.
    @DirkHondong on Twitter

  • http://www.sqlservercentral.com/Forums/Topic947481-391-1.aspx

    still exists and is the same. I posted it in SQL2005 and SQL 2008 forum but now is only in SQL 2008. It will also be published as an article on 17 Aug 2010.

  • Thx a lot.

    I already found it by searching in all your posts as well. 🙂

    I´m looking forward to read the full article.

    Your solution has a great potential and it´s a great work so far.

    Regards

    Dirk

    --
    May you never suffer the sentiment of spending a day without any purpose.
    @DirkHondong on Twitter

  • Thanks for the compliment! I'll treat myself to a hazelnut. The article is no different to this post. The code is also complete with no planned updates at this stage. I already spent loads of time on it when I could have been collecting nuts for the winter! It is operational at my site on tens of servers and is functioning fine so far (a few months). I know I don't explain the actual DatabaseAuditTrigger or ServerAuditTrigger much in the article but that was the easy part and they are well commented. The hard part was making it self maintaining/installing and coping with database restores and attaches. You can find explanations of basic triggers in many other places.

    Let me know if you have any problems with it. The main problems I have is when someone else installs it but does not create the required folder (or file permissions) for the two job logs.

  • Dear Mr.Ajay,

    Did you finally find the solution for your trigger? i myself am in need for the same.

    I have created the trigger for CREATE db but not for restore and attach.

    Can you please help !

    Thanks

    T

  • tauseef.jan (10/1/2012)


    Did you finally find the solution for your trigger? i myself am in need for the same.I have created the trigger for CREATE db but not for restore and attach.

    have you read this ? http://www.sqlservercentral.com/Forums/Topic947481-391-1.aspx

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply