Transfer Logins From SQL Server 2000 To 2005

SQL Server 2000 To 2005 Upgrade (Database Users Logins)

Hi All,

I have just migrated couple of test databases

from SQL Server 2000 to 2005 (side by side).



I also migrated the server level logins using SSIS transfer logins task, available on SSIS 'Transfer logins', I selected all the databases that I have migrated so that I have all database users account in server logins (to avoid orphaned users); but I don't have all the database users in server logins, also the sys.sysusers doesn't have the database users, that I have moved to SQL 2005. Can anybody help?



Also do I need to back up the SQL Server 2000 database and restore it on SQL 2005? What impact this operation can have?



Thanks for your time..



Bidyut

Transfer Logins And Passwords To Another Server

I€™m using express edition of SQL server 2005. 9.00.1399.06 (Intel X86)
 
I need to transfer all SQL logins and passwords from one server to another.
 
 The script  at http://support.microsoft.com/kb/246133  (designed for sqlserver 7.0 and 2000)  This script references a system table (master..syslogins) that may not exits in sqlserver 2005. I get the following error:
 
EXEC master..sp_help_revlogin
 
Msg 208, Level 16, State 1, Procedure sp_help_revlogin, Line 12
Invalid object name 'master..sysxlogins'.
 
 
Would SMO be something to try for transfering logins and passwords?
 
Thanks,
Karl

Move Logins From A Sql 2000 Server To A Sql 2005 Server

Hi guys,
I have tried to use the sp_help_revlogin script to move logins from a sql 2000 server to a sql 2005 server. It works for users that have a password. But if the password is blank it will fail. Is there a work around for this since I can't change the users passwords?
I have also tried SSIS package but that too doesn't work because of the same reason.

Can anyone help...

Transfer Logins: The Source Server Can Not Be The Same As The Destination Server

I am replacing a server so I need to migrate everything. The old server is running SQL2000 and the new server is running SQL2005. I am trying to write an SSIS solution to migrate everything for me and I can't even get started because I get the error "The source server can not be the same as the destination server". At the same time I am changing the name of the Domain so the two machines arenot even members of the same Domain. I am doing this over the Internet so the machines are not even on the same subnet. The only thing I can think of is that the machine names are the same so even though the domains are different therefore the full names are different, the NetBIOS names are the same. Could that be the problem?

Logins From 2000 To 2005

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

SQL 2000 - To SQAL 2005 Logins

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

Regarding Migrating Windows And SQL Logins To SQL 2005 From SQL 2000

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

Transfer Logins

HI,
If i transfer logins from one server to another server using dts, I am getting all the logins and permissions... How will i get passwords too..

-thank u.

--Siva

Logins Transfer

Does anyone have a sample SQL DMO script to transfer SQL Server logins from one server to another including the passwords ? any pointers will be greatly appreciated

Transfer Logins

I would like to know how I can transfer all Login and password and access records from one SQL Server to another, without transferring the databases. I know I can do this via DTS, but I can not figure out the right combination for bringing over the login names. passwords, and roles, with out transferring the databases. The databases have been transfered already via a backup and restore process. Now I need all of the login infomation from the old server to come across. In DTS I go through the Wizard saying to transfer the Master Database from one server to the other, and transfer objects and Data between SQL Server databases. Under the selcet options to transfer page, I uncheck the "Use Default Options" , and on the options page I select all of the "Security options" and deselect all of the "Table options" (Indexes, triggers, keys, etc). The process runs for a short time before bombing out with a failure. I can not find a log file under the Log folder to tell me where it has failed. Can anyone one lend me a hand with this?

TIA

Ken Merrigan

Transfer Logins - Plz Help

Hi all
In the process of migration of a database from one SQL 2000 server to another, I am transfering the logins using the microsoft documented script

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

When i am executing the script on the source server, I am getting the following error.

Server: Msg 195, Level 15, State 10, Procedure sp_help_revlogin, Line 52
'LOGINPROPERTY' is not a recognized function name.

In all the past migrations , I have used this without any issues, but not sure what have changed.

Or am i doing something really stupid.
Can you guys pls execute this in your server and see whether you are able to create the sp_help_revlogin stored procedure.

Thanks in advance
Sreenath

Transfer Logins Task

Could anybody tell me whetherTransfer logins task transfers passwords for logins or not?

Data Transfer From SQL 2000 To SQL 2005

I am new to

Sql 2000 To 2005 Data Transfer

I am trying to copy a database from sql2000 to sql2005 but it is giving me the following error:
To connect to this server you must use SQL Server Management studio or SQL server management objects (SMO)

this can be done or servers are not compatible?

How To Transfer Sql 2000 To 2005 Via Ssis

Transfer Logins Task Between Servers

As we all know logins and jobs aren't replicated when using AllwaysOn. What i wanna know is if the "tranfer login taks" will be enough to cover the transfer of logins between servers.

Transfer Logins With Passwords And SQL2005

I have versions of sp_hexadecimal and sp_help_revlogin I used with SQL 2000 to transfer logins. Will they work with SQL 2005 as-is? Might I need updated versions?

Transfer Logins From Sql2000 To Sql2005

Hi,

How To Transfer Data From Sqlserver 2000 To 2005

Hi,
  I am trying to transfer data from a sql server 2000 database to a Sql Server 2005 database... and i just want to transfer around 2 tables.. and my sql server 2000 database is located in one machine and the 2005 database is located in the other machine.. So i am trying to insert data into the sql server 2005 database. So can i do it like this..
Insert into SqlServer2005comp.Databasename..TableName
 (
   Columns
)
 Select  
   Columns
From
     sqlserver2000comp.Databasename..tablenames
Where UserId = @UserId.
Is this the right way to do it.. and can i give this query in the sqlserver2005 db or the 2000 db
any help or ideas will be appreciated.
Regards...
Karen 
 
 
 

SQLEXPRESS 2005 To SQL 2000 Data Transfer?

Hi All! i have a database in SQLEXPRESS 2005 but i need to this Database in SQL 2000...there is any way to move data from sqlexpress 2005 to sql 2000.... please help me..... 

How To Transfer Stored Procedures To 2005 To 2000

Hi gurus

i have a assignment on hands to transfer store procedures of 2005 to 2000. I have tryed by scripting SPs and then runing them in 2000 QA, but it gives me error every time ....... so please help me to resolve this problem.

thanks
shekhar

Simple Objects Transfer From 2000 Db To 2005 Db

creating a simple package and cant get it to run below is the error. when i check the source 2k db its there see below...

i am clue free of the issus...

SSIS error message

Error: 0xC002F325 at Transfer SQL Server Objects Task, Transfer SQL Server Objects Task: Execution failed with the following error: "ERROR : errorCode=0 description='FK_MoreBDRInformation_dbo_BatchDataReports' is not a constraint. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".

source Server DB sysobjects table record

name id xtype uid info status base_schema_ver replinfo parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category cache
FK_MoreBDRInformation_dbo_BatchDataReports 1618573300 F 1 0 0 0 0 86043838 2006-01-31 12:35:41.530 0 0 0 F 0 11 0 2006-01-31 12:35:41.530 0 0 0 0 0 0 0

Transact SQL :: How To Transfer Logins And Passwords From One Instance To Another

I need to transfer the logins and passwords from my prod server to my DR server. I've seen a number of articles about how to transfer logins; but, I'm not finding anything on how to transfer both logins and their passwords. SQL server R2008 R2

(Urgent) How To Transfer Data From Sqlserver 2000 To 2005

Hi,

I am trying to transfer data from a sql server 2000 database to a Sql Server 2005 database... and i just want to transfer around 2 tables.. and my sql server 2000 database is located in one machine and the 2005 database is located in the other machine.. So i am trying to insert data into the sql server 2005 database. So can i do it like this..

Insert into SqlServer2005comp.Databasename..TableName

(

Columns

)

Select

Columns

From

sqlserver2000comp.Databasename..tablenames

Where UserId = @UserId.

Is this the right way to do it.. and can i give this query in the sqlserver2005 db or the 2000 db

any help or ideas will be appreciated.

Regards...

Karen

Transfer Logins Task, Can't Set LoginsList Or DatabasesList From Variable

I need to make a general package to copy SQL databases between servers.
A package with fixed task properties work fine.
But my plan is to make a general package and use varables to handle parameters like connection strings, server names, database names etc.
My problem is the Transfer Logins Task and its collection properties like LoginsList and DatabasesList which uses string collections.
In my understanding I can't make a ordinary variable in "design mode" for string collection. For that reason I wrote some code in a Script Task to set an Object variable to a string collection. And then use the object variable to set the LoginsList or DatabasesList property.

But I get following error:
TITLE: Microsoft Visual Studio
------------------------------
Nonfatal errors occurred while saving the package:
Error at Transfer Logins Task: The data type of variable "User:ourceDB_list_o" is not supported in an expression.
Error at Transfer Logins Task: Reading the variable "User:ourceDB_list_o" failed with error code 0xC00470D0.
Error at Transfer Logins Task: The expression "@[User:ourceDB_list_o]" on property "DatabasesList" cannot be evaluated. Modify the expression to be valid.


My code to set the object variable:

Dim DBs As System.Collections.Specialized.StringCollection = New System.Collections.Specialized.StringCollection()

DBs.Add(Dts.Variables(0).Value.ToString())

Dts.ExecutionValue = DBs


Grateful for any guidance.

Regards, Jonas

Sql 2000 Copy To Sql 2005 Loses Identity Seeds In Transfer

I have a database that I am trying to copy to sql server 2005 and when I copy it the "Identity Seed" property loses its value.

I have tried to use the "Export" method by right clicking on the database then going to "Tasks" then "Export Data"
This copies all of the data but it removes the "Identity Seed" value.

When I run the "copy database" function it will also copy all of the data in the database but it will still not keep the "Identity Seed" value


I have many tables in the database with the identity seed field set to 1 on the primary key so that it will automatically create the primary key for each record.
Without this errors will occur in my web application because it thinks that I am trying to enter a null value into the primary key when it is supposed to auto populate.
When I export it with sql server 2005 it turns it back to zero.
The export function worked fine in Sql server 2000

The only way I can get it to work is by backing up the database and restoring it, but this is very time consuming and adds many steps to the process, and also makes it so that I have to overwrite my old database with the same name.

Is there a way to use the export function or the copy database function to retain the Identity Seed value

I really need this to continue do the switch to 2005 server. It seems like this may be a bug of some sort

Transfer Database SSIS Task 2000 -&&> 2005 Problem

I set up a task to do a transfer of a SQL 2000 db to SQL 2005 in Integration Services (selected my servers, dbs, and chose DatabaseOnline method). In debug mode it processes for a little while and finally errors with:



[2] Progress: Starting database transfer.. Step 1 out of 2 complete

Error: The Execute method on the task returned error code 0x80131500 (An exception occurred while executing a Transact-SQL statement.). The Execute method must succeed, and indicate the result using an "out" parameter.

Task Transfer Database Task failed

Finished, 4:01:24 PM, Elapsed time: 00:10:39.422

Have Transferred Database &&amp; Users From Sql 2000 To 2005 But How Do I Transfer Their Permissions ?

We are currently running sql 2000 and are moving our database onto sql 2005 running on a different box.

We have managed to move the entire database, with users however the users permissions on specific tables/views/stored procedures have not been transferred, does anyone know a way of transferring user permissions rather then doing them all by hand?

The system is a large (over 500 table/views/stored procedures) and a very active one and therefore downtime is not optional.

any suggestions would be appreciated

Regards

Chris V

Migrating SQL Server 2000 Logins

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?

How To Use Transfer SQL Object To Copy An Entire Database Including Logins And Users

I have read the previous threads on the bugs with this task mainly: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1438968&SiteID=1 . These are great posts that helpmed me avoid wasting time. I haven't seen one yet that addresses copying an entire database including the sql server logins.


I would like to import the ENTIRE database from one (2005) server to another(2005) using the SSIS Transfer SQL Object task (not just sprocs,tables,views and functions). I have figured out how to pull the tables,views,sprocs and functions ... by using an execute sql task to drop these objects. But I cannot get this to work for users since the user dbo cannot be dropped and guest can only be disabled. I am creating a new database (this is the database where the sql objects will be copied to) via management studio to test this. There has to ba a way to get this working ... Microsoft must have published some sort of KB article on this task or a Script Task using SMO object calls. If need be I can drop the entire database on the target machine and have SSIS recreate it.

The only reason I'm willing to take a risk with SSIS rather than backup and restore is because of time constraints (I assume the SSIS task is faster) and backup storage administration.


declare @name varchar(200)
declare @object varchar(200)
DECLARE object_cursor CURSOR READ_ONLY FORWARD_ONLY FOR
select table_name,table_type from INFORMATION_SCHEMA.TABLES
union
Select name,'SPROC' table_type from sys.procedures Where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%'
union
select name,'FN' from sys.objects where type_desc like '%FUNCTION'
OPEN object_cursor
FETCH NEXT FROM object_cursor INTO @name,@object
IF @@FETCH_STATUS <> 0
PRINT ' <<None>>'
WHILE @@FETCH_STATUS = 0
BEGIN
if @object = 'BASE TABLE'
begin
exec ('drop table ' + @name)
end
else if @object = 'VIEW'
begin
exec ('drop view ' + @name)
end
else if @object = 'SPROC'
begin
exec ('drop procedure ' + @name)
end
else if @object = 'FN'
begin
exec ('drop function ' + @name)
end
FETCH NEXT FROM object_cursor INTO @name,@object
END
CLOSE object_cursor
DEALLOCATE object_cursor

SQL Server 2000: Tracking Down Source Of Failed Logins

I was checking the logs because of some failing backups and found literally hundreds of enties for failed logins for user sa. This is does not seem to be related to the failed backups, because even after fixing the problem (disk space), the entries continue to pile up.

Needless to say, the information provided in the log is a bit sparse: date and time, source (Login), message (Login failed for user 'sa'.). If that is actually enough to go on, then there must be some way of cross-referencing this information with that found somewhere else. Is there a 'somewhere else' or are there other things I need to do in order to track this down?

I don't suspect any malicious intent, but I'm not ruling it out. My main concern at this point is twofold: to gain a better understanding of system management; to identify and correct the failed login.

Any recommendations, whether tools, documentation, or otherwise are very much appreciated.

Ron.

SSIS 2000 To 2005 Database Transfer Lost All Primary Keys, Identities And Indexes

Hi everyone, I am trying to upgrade a 2000 DB to 2005 with SSIS Database Transfer.

I finally got it to work with an online transfer but the destination database has no primary keys, indexes or identity columns from the source DB.

What am I missing?

What other ways are there to upgrade a SS2000 DB to 2005?

Thank you.