SQL Server 2005 upgrade from SQL Server 2000

Question

SQL Server 2005 upgrade from SQL Server 2000

golden-1131024

Ten Centuries

Points: 1285
More actions
October 8, 2009 at 2:21 am

#136477

Hi all,
Just to consult some questions from you all,
I did a side by side upgrade with a newly created named instance in SQL Server 2005.
I attach the current SQL 2000 DB into the SQL Server 2005 named instance.
1) I try to execute the login scripts in SQL Server 2005, the logins are created in the new instance,
however, I notice the object permission related to the login does not exist. How to resolve this
problem?
2) How can I transfer all jobs and Maintenance Plans created in the SQL Server 2000 into SQL Server
2005?
Restoring msdb seemed to require same version or build of SQL Server.
What are the other concern I need to take into account except the one I mentioned above?
Appreciate the opinion and help given.
Regards,
Golden

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

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

sql_lock SSCrazy Eights Points: 9373 More actions · Answer 1

Have you tried using SSIS within BIDS?

There is a transfer job tasks that has worked for me. I have had it where it fails but that was due to the "job owner" not existing on the destinations server.

Hope this helps

golden-1131024 Ten Centuries Points: 1285 More actions · Answer 2

Is there any other methods aside from using BIDS?

Do you have any opinion about the object permission ?

Silverfox SSC-Insane Points: 23150 More actions · Answer 3

golden-1131024 (10/8/2009)
Hi all,
Just to consult some questions from you all,
I did a side by side upgrade with a newly created named instance in SQL Server 2005.
I attach the current SQL 2000 DB into the SQL Server 2005 named instance.
1) I try to execute the login scripts in SQL Server 2005, the logins are created in the new instance,
however, I notice the object permission related to the login does not exist. How to resolve this
problem?
2) How can I transfer all jobs and Maintenance Plans created in the SQL Server 2000 into SQL Server
2005?
Restoring msdb seemed to require same version or build of SQL Server.
What are the other concern I need to take into account except the one I mentioned above?
Appreciate the opinion and help given.
Regards,
Golden

How did you generate the login script and to be honest in the past, i usually just script the jobs and run the scripts on the new instance.

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

golden-1131024 Ten Centuries Points: 1285 More actions · Answer 4

I use the method that is provided by miscrosoft website.

http://support.microsoft.com/kb/246133/

It seemed to generate the login and default DB, however it seemed not to generate the object permission.

sql_lock SSCrazy Eights Points: 9373 More actions · Answer 5

As in the previous post you can script the job... I pref to use SSIS, but that is personal pref.

You could try using "EXEC sp_change_users_login 'Auto_Fix', 'user'" to corrent any issues with the logins.

There is a free tool from idera that i've used in the past

http://www.idera.com/Products/Free-Tools/SQL-permissions/

golden-1131024 Ten Centuries Points: 1285 More actions · Answer 6

Hi sql_lock;

Sincerely apologize for the late reply.

I have tried BIDS transferring job task and it is successfully, will those jobs work properly in sql server 2005 although they are still a lot of information stored inside the msdb system db in sql server 2000?

The sp_change_users_login is to solve orphaned user, will be the permisson also be reflected back?

Regards,

ChiragNS One Orange Chip Points: 26137 More actions · Answer 7

Hi

sp_change_users maps a login to a user. By itself it will not generate user permissions. Have u transferred the users from your SQL 2000 db to SQL 2005 db? Or is it that you have transferred only logins ?

"Keep Trying"

golden-1131024 Ten Centuries Points: 1285 More actions · Answer 8

ChiragNS (10/13/2009)
Hi
sp_change_users maps a login to a user. By itself it will not generate user permissions. Have u transferred the users from your SQL 2000 db to SQL 2005 db? Or is it that you have transferred only logins ?

Ya, I use the methods mentioned in microsoft official websites, I able to transfers all server logins and I map those database logins to the server login as well by using sp_change_users_login.

However, the object permission level not to be reflected. What should I do?

Kindly advice.

ChiragNS One Orange Chip Points: 26137 More actions · Answer 9

a quick reply...

create or search a script that works on SQL Server 2000 which will give out the object permissions for each user. Run this script on SQL 2005 after modifying the script if necessary.

This is just a general idea , i myself haven't done this.

"Keep Trying"

george sibbald SSC Guru Points: 104210 More actions · Answer 10

If you attached the database it MUST have bought object level permissions across, and the use of sp_help_revlogin will have tied the users to the logins. Are you talking about permissions in the user database or in the system databases?

also be aware that when you upgrade a user database the process creates schemas for the SQL logins and makes this schema the default for the user, this might not be what you want.

---------------------------------------------------------------------