Transferring stored procedures (SQL Server 2k)

Question

Transferring stored procedures (SQL Server 2k)

stargazer74

SSC Enthusiast

Points: 181
More actions
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

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

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

Larry C Default port Points: 1493 More actions · Answer 1

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

Jeff W SSCertifiable Points: 5207 More actions · Answer 2

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

mromm SSCarpal Tunnel Points: 4301 More actions · Answer 3

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.

p.durrant Grasshopper Points: 23 More actions · Answer 4

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

barcoder SSCommitted Points: 1655 More actions · Answer 5

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.

Tony Shanks SSCommitted Points: 1654 More actions · Answer 6

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.

rstone SSCertifiable Points: 6209 More actions · Answer 7

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. 😉

rstone SSCertifiable Points: 6209 More actions · Answer 8

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. 😉

AndrewNelson SSC Journeyman Points: 89 More actions · Answer 9

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

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

stargazer74 SSC Enthusiast Points: 181 More actions · Answer 10

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

Jennifer