May 30, 2007 at 9:49 am
I am going crazy with this new interface and want to smash the computer ;D. I have been given a task to "fix" a collation issue with a SQL 2000 to SQL 2005 upgrade. During the upgrade the "new" application had a different default collation than the old SQL 2000 database...which as you might have guessed is causing errors with queries...
SO...my plan was to script out the database, tables, objects, users...ect (create a db shell) and then import all the data into the two new databases with the correct collation. Simple enough right...
WRONG...I have been struggling with the scripting interface when compared with SQL 2000.
My initial plan was as follows.
1. Script out the two databases with all the objects & permissions (which I can't figure out)
2. Detach the two current databases.
3. Rename the physical files (Ex. Database_I_TEMP.mdf & Database_II_TEMP.ldf)
4. Reattach the databases with different names than the production databases (Temp_db_I & Temp_db_II.
5. Run the scripts to re-create the production databases (all users, objects, permissions).
6. Run the simple Integration services Import wizard to import the data from the old (incorrect) databases to the new (scripted out with correct collation) databases.
My issue revolves around the fact that I am not familiar with all the new settings, have had no training and am attempting to stumble through this process.
I want to script out all the objects...so I have gotten the DB, the Tables, the Stored Procs, views, User Defined Data Types...ect, that appears to be working just fine.
When it comes time to script out the db users, schema's, permissions that are present they just create the users...NOT PERMISSIONS...
From what I can see the schemas that are attached to these users don't have any rights "assigned"...so I don't see how the schema correlates to the actual user account.
How do you go about scripting out the permissions (like db_datareader/db_datawriter...or permissions to objects like stored procs). Do I have write that code myself?
Any help would be appreciated because I am going in circles...
Thanks,
Lee
May 30, 2007 at 12:43 pm
Lee,
in SSMS if you select the "generate scripts" task on the third page in the wizard (after selecting the database) you can set all kind of options. In your case you ned to set the option "script object-level permissions" to True. If the schemas "attached"to the users are not used you don't need to script those. SQL2005 automatically creates a schema for each user during conversion from 2000 in case the user was the owning any objects.
Hope this helps
Markus
[font="Verdana"]Markus Bohse[/font]
May 30, 2007 at 2:01 pm
After writing my post I found a good article that explained what a "Schema" is and how it is used in 2005. Apparently all the other big db environments utilize this process and it is just now that MS is following suit correct?
From what I read, if you take a 2000 database and restore it on 2005 it does exactly what you described...by creating a default schema for the user and I "believe" assigning the permissions to the objects to the schema...and finally make the user a member of the schema...
Hope that last part isn't too confusing...
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply