April 18, 2008 at 7:43 am
I recently upgraded from SQL Server 2000 to 2005. In 2000 I could generate SQL scripts to create the table, including permissions. I would paste that script into Query Analyzer, change the name of the table, and execute to produce a new table with identical design and permissions.
In 2005 I create the script into the Query Editor Window, change the table name, and execute to produce a new table. The problem is that the permissions aren't copied.
Does anyone know of way to copy the permissions from one table to another? I've been checking BOL and the forums here, but haven't come up with anything.
April 18, 2008 at 7:48 am
In Management Studio, under Tools, Options, Scripting Options, there is an option to Script Permissions. It defaults to False. Change that, and you'll have what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 18, 2008 at 7:58 am
Thanks for the prompt response!
Unfortunately, I don't have Scripting Options as one of my choices.
April 18, 2008 at 8:04 am
The Options window should have choices for:
Environment
Source Control
Text Editor
Query Execution
Query Results
Scripting
Designers
Does it not have all of those? If so, which version of SQL Server are you using? (Or which version of Management Studio? That might be more relevant.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 18, 2008 at 8:10 am
Check out the attached document and you can see what I'm getting.
April 18, 2008 at 2:47 pm
Can't help you on that one. I didn't know there were different versions of the Options module for different versions of Management Studio. Maybe someone else can point you in the right direction.
Are you, by any chance, using SQL Express Management Studio? I'm using Dev Edition. Might that be the difference?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 18, 2008 at 3:04 pm
I have the same version of SSMS on one of my desktop systems (I have SQL Server 2005 DE installed, and on this one system I can't get any SP to load!) and I see exactly the same thing. I poked around, and I couldn't find anywhere to modify scripting options. I'd suggest (if possible) that you look at upgrading your installation to a newer version.
Sorry I can't help out more.
😎
April 22, 2008 at 6:02 am
I believe I found the answer: you launch the script wizard by right clicking on the database and selecting Generate Script. The wizard will then allow you to select a number of options, among them permissions.
Wow, that was intuitive.
April 15, 2016 at 9:28 am
Actually, you right click on database, select Tasks, then select Generate Script . . . and then select "Set Scripting Options" and then click on the "Advanced" button which will bring up the "Advanced Scripting Options" menu. Permissions is there.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply