Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
insanepaul
Posting Yak Master
178 Posts |
 Posted - 2011-06-02 : 06:57:54
|
| I've written a script to alter a load of stored procedures by using if exists drop SP then create it with my changes but I noticed I then lose the permissions that were set on it.These permissions were set before my time. I'm thinking to alter them manually not in a script but this may lead to human error. What would you do? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-02 : 07:05:02
|
| I usually include the grant statements in the SP script.Another option is to create a table that has all the permissions in it and run an SP on that to update them whenever you make any changes.If you do an alter on the sp rather than drop and create then the permissions remain.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-06-02 : 07:06:41
|
| Instead of dropping and re-creating the stored procs, if you alter them, the permissions will be preserverd. See here: http://msdn.microsoft.com/en-us/library/ms189762.aspxIf you must drop and recreate, perhaps you need to capture the current permissions, drop, recreate and then give the permissions again. You can look up the permissions using sys.fn_my_permissions (http://msdn.microsoft.com/en-us/library/ms176097.aspx) |
|
|
|
insanepaul
Posting Yak Master
178 Posts |
Posted - 2011-06-02 : 07:58:07
|
| Thanks |
|
|
|
|
|
|
Dropping/Creating SP loses Permissions