November 28, 2011 at 7:34 pm
Hi,
I need to script my local database to my client's server. I need to script in such a way that if the sp / table exists in the client machine it should alter them else create them.
I tried to generate scripts with "if exists" and "drop" which works fine. But by this way, if an sp is dropped and recreated, the access rights given to users on sp's will be lost.
Is there any better way. Kindly advise.
Thanks,
Suraj
November 28, 2011 at 10:11 pm
You can try doing an alter proc if the proc exists. Else you can script out all perms and then reapply perms post script update.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 28, 2011 at 11:18 pm
suraj.app (11/28/2011)
Hi,I need to script my local database to my client's server. I need to script in such a way that if the sp / table exists in the client machine it should alter them else create them.
I tried to generate scripts with "if exists" and "drop" which works fine. But by this way, if an sp is dropped and recreated, the access rights given to users on sp's will be lost.
Is there any better way. Kindly advise.
Thanks,
Suraj
Try this workaround:
Generate script to create the SPs.
Run the script. It will create the SPs that don't exist. Throw error for the ones already existing.
After that, replace the key word CREATE with ALTER using Find and Replace.
Run the script. It will update all the SPs including the old ones.
November 30, 2011 at 4:36 am
Tools ~ Scripting Options ~ Object Scripting Options ~ Script Permissions
set that to True, then you won't lose the access rights.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply