Regarding Migrating Windows And SQL Logins To SQL 2005 From SQL 2000
Jul 27, 2006
Hey. I've a script which gets the SQL usernames with the information I need. Is it possible to migrate the passwords also with this information?
Also, regarding Windows Logins, do i have to worry about their SIDS when migrating to SQL 2005. I'm going to attach SQL 2000 db's onto a 2005SQL instance and these db's alreayd have the usernames assigned in them. Please let me know if there is a way out for the above two questions. thank you.
SELECT 'Create LOGIN [' + loginname + '] with password = ''hello'', sid = ',sid,
', default_database = ' + dbname + ', default_language = ' + language + ',check_expiration=off,check_policy=off'
FROM master..syslogins
WHERE name NOT IN ('sa')
AND isntname = 0
View 6 Replies
ADVERTISEMENT
Mar 27, 2004
I'm changing servers and want to copy all the logins from the old server to the new server. Is there anyway to do this?
View 5 Replies
View Related
Apr 3, 2007
I am a systems analyst and work with an app that runs against 2 SQL Server DBs. Though I have some familiarity with SQL Server and SQL, I am not a DBA.
The app executable is tied to a Windows service.
When we install the app, we run a process that builds 2 dbs to include:
Tables, indexes, stored procedures, views and user accounts.
SQL Server is set up for mixed mode authentication.
Normally, the dbs run off the local db user accounts which are tied to local logins with the same names.
We have a client that wants to remove our standard logins so that they can run on only a Windows login.
I know I should be able to tie the db users to a Windows login.
And I can do the same for the service.
But I am at a loss as to how to get this done.
How do you associate db users with a Windows login?
When I have tried sp_change_users_login I get an error that the Windows login does not exist. (Though I have added the Windows account to the DB.)
Hope this all makes sense.
View 2 Replies
View Related
Jun 26, 2003
We have a "spanking new" machine to run SQL server and moving the databases over was absolutely no problem.
However, we discovered that we left the user ids and passwords behind.
Is there a way to get them copied from the old database to the new one?
View 7 Replies
View Related
Oct 6, 2004
When using the script to transfer logins and passwords from one server to another. After doing so... Does the logins and passwords still appear on the server transfering from??? I am doing a parallel install and need to keep the production database logins and passwords still intact. Thanks...
I did a detach on each database/logs.
Moved to the exact location on the new server.
View 4 Replies
View Related
May 15, 2007
Hi all,
I'm in need of migrating DB's from a SQL 2000 server to a new SQL 2005 server. Has anyone had any success in this or does anyone know of a process which would be best for this situation?
Here's my setup...
SQL2000 SERVER....OS - Server 2000
MDF's/LDF's reside on D:MSSQLData
Backups reside on D:MSSQLBackup
SQL2005 SERVER....OS - Server 2003
MDF's/LDF's reside on E:MSSQLData
Backups reside on E:MSSQLBackup
Any information on how to get these DB from 2000 to 2005 will be greatly appreciated.
View 9 Replies
View Related
Jun 12, 2007
Hi, since havenot done migration yet, my company is planning to migrate from sql 2000 to sql 2005 server. and i am in group for capacity planning. can anybody suggest me the steps i should be really taking in order to migrate to 2005, if anyone has done the it in real world with database up to 50g.
thanks
Derek
View 1 Replies
View Related
Mar 26, 2008
Guys! Very very junior in this migration part. Hope the seniors can help out....PLEASE!!!!!! Don't get me wrong asking you guys to spoon feed me. I have read a lot of articles and even BOL and also not to forget read millions of books on this. But just wanted to make sure that I am in the right path and want to do this without any problem. That's why I have decided to ask the Gurus here who have done this in past.
I have this machine now:
Microsoft SQL Server 2000 - 8.00.2040 (Intel X86)
Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: )
Migrating to this machine:
Microsoft SQL Server 2005 - (X64)
Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
I have these in the SQL Server 2000 box:
1. Databases, tables (of course)
2. Views
3. Securities(Logins, Roles, Password)
4. DTS packages
5. Jobs
6. Replications
7. Linked Servers
8. Stored Procedures
Every article is different. I was confused at a point of time. Please show me the right way guys!
View 8 Replies
View Related
Mar 27, 2008
Yesterday I started this topic and I came step by step higher and now I have finalized my steps to migrate.
Seniors and Gurus and who has done this before, please review and correct me where I am wrong and/or add some tips for me to have this one going on successfully! Thanking all of you guys in advance! Here is what I have:
Before Migrating:
1. Install Service Packs, Security Patches and Hot Fixes
2. Install DTS Designer Tool from Microsoft to prevent conflict and failure of migrating current DTS packages to SQL Server 2005
3. Backup EACH and EVERY Database and double check the .bak files whether it exist and backup was successful
4. Just before migrating, reboot the server to clarify that the SQL Server Services and SQL Server Agent is working
When you are ready to migrate:
5. Make sure there are no users trying to connect to the OLD AND the NEW SQL Server. Inform them well in advance about the downtime. Shutdown any applications or websites that might be accessing the SQL Server.
6. Stop the SQL Server, SQL Agent and MS Search Services on the NEW SQL Server
If downtime is not possible:
7. For ever changing OLTP database, by the time you restore the backup onto the new server, the old server would have received more changes. To avoid this problem, you need to resort to log shipping. Log shipping is nothing but an automated process of backup and restore. The following steps will explain the process of log shipping (Note that SQL Server 2000 Enterprise Edition provides inbuilt logs hipping. We don't need that for this purpose. We can simply create a job that backs up and restores the databases
8. Create a job on the old server, with two steps. The first steps backs up the transaction log of the OLTP database. The second step restores this transaction log backup file onto the new server, using WITH STANDBY or WITH NORECOVERY option of the RESTORE command. Schedule this job to run every 10 minutes or 15 minutes, depending on the volume of transactions your OLTP database receives
9. This job will take care of shipping all new transactions since the full database backup, to the database on the new server. Let this job run until you can afford some downtime on your production database (that is, mid-night, or early morning, or a planned maintenance window)
10. Now to be doubly sure, no new transactions are coming in, set the database in 'single user' or 'dbo use only' mode. Take one last transaction log backup of the production OLTP database, and restore it onto the database on new server. This time use the WITH RECOVERY option of the RESTORE command
11. Run UPDATE_STATISTIC on User Database to reorganize all indexes
Considerations:
12. Security - The security model changed along with object ownership, but users that own objects will be turned into schemas and all of the object and role permissions will come over. These you will want to look over afterwards, but they should be ok.
13. Unless you have hundreds of them, it is always better to re-create them after upgrading. You cannot detach an MSDB database on a 2000 server and attach it on a 2005 server and have it work. Fortunate in this, we can script out the jobs from SQL Server 2000 and run it on the new SQL Server 2005
14. Typically you will have to re-create your replication too. Again, the good news is that you can generate the scripts for replication on a 2000 server and execute them on a 2005 server and it will create the appropriate articles and publications
15. For DTS packages, it is always recommended to use the Package Migration wizard to do this so that less or no problems will be encountered. Consider about the jobs previously set.
16. Logins can be created by using sp_hexadecimal and sp_revlogin2005
View 6 Replies
View Related
Jun 21, 2007
Hi Friends,Is there any tool or assistant to upgrade SQL 2000 to SQL 2005.Thanks,Arunkumar
View 2 Replies
View Related
Mar 29, 2007
I would like to find out what the steps are to migrate a 2000 database up to 2005?
View 4 Replies
View Related
Aug 14, 2007
Hello guys, I have vry basic question. I'm migrating from 2000 to 2005. MY question is do we have to recode all packages or just migrating of old 2000 DTS is fine. If so, then I can see only small icon of DTS package in 2005, whihc is not showing the details of DTS. Kindly, explain me what do i really do, migrating old one or re-creating new in 2005. Thanks in Advance
View 1 Replies
View Related
Jun 22, 2007
As from what i see there are different system files for logins.
Is there a way to transfer SQL 2000 logins to SQL 2005 i am used to the
sp_help_revlogin i think thats the script..in 2000
Is there a way to convert all logins from SQL 2000 to SQL 2005
View 3 Replies
View Related
Feb 6, 2007
HiI need to move a couple of SQL logins to a new machine to support a3rd party application. Problem is the password is embedded in theapplication and we can't see how to change it. Does anyone have amechanism for moving a login to a new machine *without* knowing orchanging the password?TIAChloe
View 1 Replies
View Related
Apr 2, 2008
Has anyone had experience of migrating from 2000 to 2005 on the same box , and maintaining the SERVERNAMEINSTANCE_NAME.
What are some effective ways of migrating and retaining the same servername/instance name ?
The issue revolves around minimising the use of extra boxes , as I would like to do the migration on the same server and keep the name the same .
Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com
View 3 Replies
View Related
May 23, 2007
Hi,
I have a database on a SQL Server 2000, and would like to migrate it to a new server machine that has SQL Server 2005. has anyone already tried this, and can you please provide the steps to follow.
Thank you.
View 7 Replies
View Related
Sep 28, 2007
Hi,
We are migrating from SQL Server 2000 to 2005. We currently use ADO.Net to make connection to sql server. I just want to know after migration to sql server 2005 do we have to make any changes in code in the way we make connection to use ADO.Net2 which is part of sql server 2005. ??
Thanks
Arvind
View 1 Replies
View Related
Jan 5, 2007
Hello All,
I am working on a migration project of my database(lets name it DB1), from SQL 2000 to SQL 2005. I have some DTS packages in my SQL 2000 database which also needs to be migrated to SSIS. These DTS packages currently interacts with other database(lets name it DB2) which is also in SQL 2000 and which does the data transmission. The real issue is that the other database(DB2) also has some DTS packages which also communicates with DB1 (the db to be upgraded) and so when i migrate my DB1 to SQL 2005, i will have to change my DB2 packages also (although the change will be minor). Now considering that in future I might also migrate my DB2 to SQL 2005 I wanted to know what will be the right approach to follow. One is to modify the DTS packages of DB2 to accmodate the change in connection and the other is to migrate the DTS packages of DB2 also to SSIS.
Can anyone help me with this one.
Thanks in Advance
Mitesh
View 5 Replies
View Related
Feb 10, 2008
Hi guys
We are in the process of moving from SQL Server 2000 to 2005. In this process in general I have noticed that performance is better as a result of the move but in a couple of specific cases performance is about 10 time worse as a result of the move and i am wondering if anyone can tell me why.
1) Should I be noticing that calling functions from within a where clause are slower in 2005.
2) Has the and/or logic processing been changed between the different versions.
3) Why does this segment of code run really slow in 2005 but really fast in 2000 (note, i know that its not nice looking but it is pre-existing code from before we came on board and there are more examples of these so its a bit of a change to go through and fix it all up to what it should be but i need to know why before i can move on and as i said i know its not nice and one should expect it to be slow but i specially need to know why it would run fine in 2000 and not on 2005):
.....
AND (Deleted = 0)
AND (DATEDIFF(d, dbo.GetStartOfDate(ReviewedDate), dbo.GetStartOfDate(GETDATE())) = 3)
OR (ProgressPointId = 32)
AND (Deleted = 0)
AND (DATEDIFF(d, dbo.GetStartOfDate(ReviewedDate), dbo.GetStartOfDate(GETDATE())) = 3)
OR (ProgressPointId = 30)
AND (Deleted = 0)
AND (DATEDIFF(d, dbo.GetStartOfDate(ReviewedDate), dbo.GetStartOfDate(GETDATE())) = 3)
....
Thanks for your help.
Anthony
View 5 Replies
View Related
Aug 3, 2007
I backup SQL 2000 DB and Restored to SQL 2005
Under the Database Name, Users when i click properties the login name is blank.
When i restored from SQL 2000 to another SQL 2000 Server i would run an orphan fix
declare @usrname varchar(100), @command varchar(100)
declare Crs insensitive cursor for
select name as UserName from sys.sysusers
where issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
order by name
for read only
open Crs
fetch next from Crs into @usrname
while @@fetch_status=0
begin
select @command=' sp_change_users_login ''auto_fix'', '''+@usrname+''' '
exec(@command)
fetch next from Crs into @usrname
end
close Crs
deallocate Crs
I changed the sys.sysusers from sysusers.
But when i run this it does not work.
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
Does anyone know how to fix the logins for all of the users rather than one at a time for SQL 2005
Thanks
View 9 Replies
View Related
Apr 12, 2000
I need to move several databases to a new server while retaining the same logins/permissions. Books Online indicates that DTS can move the SQL Server logins, but it sets the passwords to NULL in the process. Is there any way to move the logins and keep all passwords/permissions intact?
View 2 Replies
View Related
Sep 16, 2004
I have a developer who created a DTS package using an NT authenticated ID. Now that we've migrated the SQL Server to another Domain in Active Directory, I am showing the following error for his jobs:
"The job failed. Unable to determine if the owner (DomainUSer) of job EmployeeDump has server access (reason: Could not obtain information about Windows NT group/user 'DomainUSer'. [SQLSTATE 42000] (Error 8198)).
When I went to check and see what the permissions problem might be, I noticed that all of my NT Authenticated ID are broken after migration - in other words, I can add the Active Directory NT ID's to the SQL Server instance, but I cannot give these ID's permissions to databases because it says they already exist??? I know if I have a sql login that is 'orphaned', I can use "sp_change_users_login" to fix it, but I think I read that this won't work for NT authenticated IDs.
IS there a way to fix this short of dropping ALL of my NT authenticated logins and recreating them????????
View 3 Replies
View Related
Mar 26, 2007
whenever i modified the connection of my current database...I'll always got an error,when asking for "server name"..it says [DBNETLIB...etc]Named Pipes etc error..my current database was SQLEXPRESS and i want to change it as an SQL OLEDB,2000,2005.. but as i said il always got an error..sometimes it says "SQL server does not exit..ODBC" etc...when im entering the "server name"..so i could only used Access or SQLEXPRESS server database.. what should i do with the error?? thanks for helping again..
View 6 Replies
View Related
Oct 25, 2007
Morning am very new to analysis server.
I have a 2000 sql server box which has two analysis databases.
I am migrating them to 2005 sql server and am using the wizard but am getting an error message on the last part of the migration.
"Mining models:
Test Miner
My OLAP Server;MIS Data Warehouse;Test Miner;Documents;All: The source object is not valid."
Any ideas.
View 4 Replies
View Related
Jan 25, 2008
Hi there
I heard from someone that there is a tool that can verify/check whether the database can be deported to SQL Server 2005 native (90) or not. Is this true?
Thanks
View 1 Replies
View Related
Feb 6, 2008
Hi,
Currently we have SQL server 2000 database for past 3 to 4 years. Now planning to move SQL Server 2005. The SQL 2000 database will be restored to 2005 server.
My questions are,
1. The SQL 2000 queries, Stored proc and views will work with SQL server 2005 without any changes?
2. Do the SQL server 2005 have backward compatability with SQL Server 2000 or we need to convert any specific SQL script after converting to 2005.
3. Is there any issues after conversion?
Please help me in this area.
Thanks in Advance!
Thanks,
Anbu
View 5 Replies
View Related
Mar 28, 2008
Hello,
I need to move a large number of reports from a 2000 reporting services install to 2005. I've used the RS Scripter utility with some success. The issue I'm having is migrating history.
Can history be migrated without upgrading the reportserver and temp db to 2005? We'd like to preserve our 2000 environment while we move...although I suppose we could create another RS instance and copy the database, do the upgrade - but how would we merge our existing 2005 install with the upgraded 2000.
View 4 Replies
View Related
Jun 7, 2007
Hi Everybody!!!!
Do I need to consider investing in new and more powerful hardware when migrating from SQL 2000 (or any other previous version) to SQL 2005?
I have a friend / client who migrated from SQL 2000 to SQL 2005 sp 1. The performance of the server got really bad, and now he's trying to fix the problem by any means necesary.
I think it would be very understandable if investing in hardware would be recommended, as we all have seen that there are so many new platforms to use and integrate to (.NET framework, XML, soap, ADO.NET, etc, etc,) and keeping the old platforms as compatible as always (ADODB, ODBC, etc.)
Any feedback is greatelly appreciated!!!!
Tarh Ik
View 1 Replies
View Related
May 13, 2008
I have successfully used migrate wizard to migrate DTS pacakge to TrainingDTS.dtsx.
What should I do next? when I run
C:>dtexec /file "C:TrainingDTS.dtsx"
Error: 2008-05-13 09:14:31.36
Code: 0xC0029172
Source: File Transfer Protocol Task undefined FTP Task
Description: The connection is empty. Verify that a valid FTP connection is p
rovided.
End Error
Error: 2008-05-13 09:14:31.36
Code: 0xC0024107
Source: File Transfer Protocol Task undefined
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
View 2 Replies
View Related
Jan 8, 2007
Hi,
Can anyone point me to any reference material, resources etc. on migration of SQL Server 2000 Reports to 2005?
Thanks,
Dave
View 3 Replies
View Related
Mar 17, 2008
I am little bit scared that if i upgrade reporting server db to 2005, My report won't work. I just wanna test with some reports in SQL 2005 reporting services.
1) what should i do( I have already installed Reporting services in 2005 (64-bit)?
2) Can i backup Report server db and Report Tempdb database and restore in SQL 2005 but these database already exists in SQL 2005.?
3) If i upgrade these database , Will it effect my 2000 Production Reports?
4) i donno what to do
Please help SQL reporting experts.
View 2 Replies
View Related
Apr 4, 2006
Hi,
I have to create a migration package ..means package should migrate the sql server 2000 tables to 2005 tables (Not dealing with data at this point of time and ignoring SPs,DTS packages).But there are lot of normalisation ans schema changes in 2005 compared to 2000.Like,
- One 2000 table devided into 3-4 tables in 2005
- Lot of changes in the filed names
- Handling integrity relationship between the newversion tables
Being new to SSIS ,iam in confusion like how to start and where to start.can you pls tell me the steps(Structured way) i have to fallow
-- I have around 8-9 tables in 2000 ,I have to migrate them into 18-19 tables (with some new fileds )
-- For each table i have to create one package(bcoz lot of transformations are there) or I can create one package for all of those ? but the finally i have to handover one package to the client
pls ask me if u need any further info to come up with the explanation..bcoz iam not sure whether i provided enough info or not
Thanks for ur help
Niru
View 2 Replies
View Related
May 13, 2008
Hi
How to transfer logins from 2000 to 2005?
Raj.
View 1 Replies
View Related
