Post reply

Transferring stored procedures (SQL Server 2k)

  • October 8, 2003 at 11:48 am

    #65497

    I've created stored procedures in a development database and want to copy them to three other databases. What's the best way to do that? If I script them, how are the current permissions on that stored procedure affected and can I create additional permissions on them in the script if needed?

    Jennifer

  • October 8, 2003 at 12:14 pm

    #477612

    You can script the stored procedures. However, you will need to add permissions again. Use the GRANT EXECUTE ON ... statement.

  • October 8, 2003 at 2:00 pm

    #477626

    As a general rule around here, I have all of the stored procedures scripted with the GRANT EXECUTE permissions at the bottom of the script.

    But, there are plenty of ways to move the procedures; one of which includes DTS (which can pass the permissions and even the logins if needed)

    There are a number of ways to create the script. sp_helptext, syscomments, ctrl+c through Enterprise Manager, etc.


    "Keep Your Stick On the Ice" ..Red Green

  • October 8, 2003 at 4:32 pm

    #477649

    Please save your stored procedure source code in files as you type those the first time. Then store those files of SP source code in a safe place (say, version control system like VSS). Permission granting statements could following the SP code in those files after a GO statement.

  • October 9, 2003 at 4:46 am

    #477683

    use a program like sql compare from http://www.red-gate.com

    it will keep all you server's in sync

    best $200 i ever spent

  • October 9, 2003 at 6:11 am

    #477690

    We're doing all of these.

    The GRANT EXEC in the stored procedure script and then using VSS is the only way we have found of ensuring that the permissions are retained on the target instance.

    SQLCompare is great for identifying differences and for copying the stored procedure across, but it does not seem to copy over any permissions and these need to be reapplied manually.

  • October 9, 2003 at 7:59 am

    #477714

    Enterprise manager - Generate SQL Scripts

    Select the procs you want to script. In the options tab, select Script object-level permissions. If you haven't already put in the users and roles, then also select Script database users and database roles.

    This will create a script with all the GRANT EXECUTE statments you need.

  • October 9, 2003 at 8:38 am

    #477730

    We have about 20 dev, stage, and prod SQL Servers and many developers - about a dozen dbs per server. We found the best way to handle permissions on all objects, including procedures, is to base access only on roles. As long as the roles are defined in all the destinations, dts can move the object permissions. We NEVER want to check the options to move users or logins - what a mess that would be for us. The developers set the permissions, hopefully, and we move them.

    It takes a while and a lot of work to remove or move the object permissions from users to roles, but it’s worth it. If you have the choice, it’s better to start with role based security.

    RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

  • October 9, 2003 at 8:45 am

    #477731

    One more thing. If your developers give you scripts to move procedures, make sure any permission grants are outside the procedure, not part of the procedure. Also, do not expect them to be experts when it comes to permissions - you might need to help them with the strategy.

    RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

  • October 22, 2003 at 8:12 pm

    #479202

    DabCos is a great tool for comparing databases. It is an open source project which can be found at

    http://www.davidemauri.it/dabcos/

  • October 23, 2003 at 10:20 am

    #479330

    Thank you for all of your ideas and suggestions. Really appreciate the feedback.

    Jennifer

Viewing 11 posts - 1 through 10 (of 10 total)

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