Post reply

Run sql when a stored procedure is created

  • October 6, 2004 at 2:47 pm

    #102230

    I just realized I probably put this in the wrong forum and am re-posting here

    I would like to run some sql when a new stored procedure is created in a DB. Is there an easy way to do this. I tried a trigger on the sysobjects table but apparently that is not possible.

    Thanks

  • October 6, 2004 at 3:26 pm

    #525659

    I do not know of a way to execute T-sql when you create a stored proc.

    You could however, run a job periodically that looks at the create date (crdate) in sysobjects for xtype='P' and performs t-sql based on the objects it finds.

    select name from sysobjects where type = 'P' and crdate = getdate()

    This will not get procedures that are changed with the alter statement.

  • October 6, 2004 at 3:29 pm

    #525662

     I thought of that, but the scheduled execution is not an option. We have some very specific security definitions on our Stored Procedures and I need to set a bunch of access rights when a proc is created based on some criteria stored in another table. This has to happen as soon as the Proc is created.... Any other ideas?

    Thanks for the feedback,

    Mike

  • October 6, 2004 at 3:33 pm

    #525664

    Why not simply require the permissions are set by whatever process is executing the CREATE PROC[edure] statement when the stored procedure is created? Most change control procedures do this to ensure permissions are not missed. Is it an automated process that is generated the SQL Server permissions or a human running a script?

    K. Brian Kelley
    @kbriankelley

  • October 6, 2004 at 3:40 pm

    #525671

    Sorry,

    Here is a bit more detail. Our developers have rights to create Stored Procedures but cannot control what rights are assigned to them. We have a table that defines rights based off of the database and prefix the sp is created with. For example a SP named hr_getUserId would be assigned certain rights depending on the DB it was created in and the prefix HR. So a developer creates a SP and then cannot test it in the application until the rights are assigned. So far this has been a manual process, the developer runs a post creation script that sets the rights. Well they are all complaing because of the extra steps. I would like to have it so that when they create one, it all happens in the background and as soon as it is created. Maybe I am approaching the problem incorrectly.

    Thanks,

    Mike

  • October 6, 2004 at 3:53 pm

    #525675

    I'm not sure how convenient your "post creation script" is to use, but it sounds like you just have really whiny developers. I'm saying that as a developer. If the script is cumbersome perhaps it can be placed in a stored procedure that the developer simply has to EXEC. That could basically be done in one of two ways that I see. You could add a parameter to the SP for the name of the SP to be given rights. Depending on the environment, it could also be written to take zero parameters but to instead just query the catalog for any SPs that have not been granted any rights yet and then perform the appropriate grants to each one. You may also want to keep the name of the SP really short and easy. It could ultimately come down to having the developer run "exec gsp" (grant stored procedure). If the developer complains about that being inconvenient feel free to slap them for me.

  • October 6, 2004 at 3:54 pm

    #525676

    This sounds like a development process in production, which is generally frowned upon. From a change control perspective you're looking at a developer having the potential of putting in a bad stored procedure site unseen. There aren't any automated mechanisms within SQL Server to handle such a requirement, hence the suggestion of others to do some sort of scheduled process to handle this. Is running every 5 minutes (or even every minute) too long a delay?

    K. Brian Kelley
    @kbriankelley

  • October 6, 2004 at 3:58 pm

    #525679

    Nice!

    You are exactly correct. I have a noparam stored proceedure they just have to execute. Takes about 3 sec. I will slap them for you! They are complaining because our environment has grown about 20 fold this last year and with each expansion, another task has to be completed to test. This has slowed development a bit and so they are feeling a bit crunched with the added work and ever increasing projects. If you can think of any other options let me know.

    Thanks,

    Mike

  • October 6, 2004 at 4:01 pm

    #525683

    This is on our test system, not production, they have no access to production. Our test system mirrors production though and so the rights are inforced.

    Mike

Viewing 9 posts - 1 through 8 (of 8 total)

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