SQL Server Performance

Running Stored Proc on DB Creation.

Discussion in 'General Developer Questions' started by RupertS, Sep 22, 2005.

  1. RupertS New Member

    As per subject - I want to run a stored procedure when a new database is created (using SQL2K).<br /><br />Why:<br />This sp creates a default user/pass and role for that database (along the lines of user_&lt;dbname&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' /> as per our in house development standards.<br /><br />Plan A.<br />Write a trigger on master.dbo.sysdatabases <br />- But this is bad practice - and the security settings won't let me (if I can change the security - then tell be how).<br /><br />Plan B.<br />Some other way<br /><br />NB.<br />I cant simply add the user to the model db as the user name is different for every db.<br /><br />Any ideas?
  2. FrankKalis Moderator

    Forget Plan A! You would need more than one workaround to create a trigger on this system table. And even though it's possible, it is unsupported by MS and not guaranteed to fire.

    Plan B: Are databases created in your environment "on-the-fly"? Do you really need this or can you implement some standard that the users come up to you with a CREATE DATABASE request, and then you'll do the rest. Also, I don't think in a secure environment, the common user should haev such escalated privileges as creating databases.

    Can you maybe give some more informations?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  3. RupertS New Member

    Frank,

    Thank you for taking the time out to reply.

    This is a development server, so this was just away of ensuring that the developers follow standards - of course this could checked via peer review - but this makes life easier for me (the manager)! Additionally we are currently creating quite a number of databases on this server so this would be make life easier for everyone.

    Plan B is for the developer to manually run the stored procedure (post to creating the database) which would create the relevant users and roles - which is looking like the only option at the moment.

    Rupert
  4. FrankKalis Moderator

    Okay, a development environment is certainly something different. If it crashes, you don't lose valuable data and are able to just restore an image to setup up the environment again. In that case it might be feasible to put a trigger on sysdatabases. <br />I don't done this what you will read in Example 2 here<a target="_blank" href=http://www.sqlservercentral.com/columnists/rmarda/letsblockthedba.asp>http://www.sqlservercentral.com/columnists/rmarda/letsblockthedba.asp</a> myself yet on sysdatabases, but I've tried once the examples in that article and it worked. <br /><br />Please allow me to re-emphasise again that this is *no solution* suitable for any production environment! And in case you're moving to SQL Server 2005 soon, I think it won't work either as many thing regarding system tables, permissions... have changed there. <br /><br />However, if you decide to try this, I would appreciate your feedback here, how it's working. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  5. Adriaan New Member

    Add the items to the MODEL database, and they will be included in all new databases.
  6. RupertS New Member

    Frank,

    I now believe that this is impossible (see article below).

    http://www.sql-server-performance.com/q&a129.asp

    What ever I do I get the following error message.

    Server: Msg 229, Level 14, State 5, Procedure trg_SysDatabases, Line 6
    CREATE TRIGGER permission denied on object 'sysdatabases', database 'master', owner 'dbo'.

    Rupert
    ----------------------------------------
    Adriaan,

    As per the first post: I cant simply add the user to the model db as the user name is different for every db.

    Rupert
  7. FrankKalis Moderator

    Rupert, read closely through the article, I've referenced. It's kinda nasty hack to the system tables, but doable. [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  8. RupertS New Member

    Frank,

    Tried the following - not sure if this is what you were getting at.

    This allows me to add the trigger - but it doesn't fire ;-(

    Any more ideas - or am I up the wrong street?

    Rupert

    USE Master
    go
    EXEC sp_configure 'allow updates', '1'
    GO
    RECONFIGURE WITH OVERRIDE
    GO
    update sysobjects set xtype='U' WHERE name = 'Sysdatabases'
    go
    create trigger trg_BuildSecurity on sysdatabases for insert
    as
    ---code goes here----
    go
    update sysobjects set xtype='S' WHERE name = 'Sysdatabases'
    Go
    EXEC sp_configure 'allow updates', '0'
    go
    RECONFIGURE
    Go
  9. FrankKalis Moderator

    No, the code looks okay to me. But it seems that now comes into play what I've mentioned in my first reply. No guarantee that they will fire. So, it seems that this method won't work.

    I can't think of any other method right now.
    Maybe a small app with one form where the developer enters all relevant information and when hitting OK, you dynamically build your SQL string according to your standards and execute it.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  10. Adriaan New Member

    Look at sp_procoption in BOL. There's only one valid option (at least documented) 'startup' meaning that the sp will execute when SQL Server starts up. Now if only we could set this for when a database is brought online ...

    I guess your developers will just have to learn to behave and stick to the guidelines.
  11. RupertS New Member

    Oh well thanks everyone for your help..

    Looks like it can't be done!

    Rupert

Share This Page