We are looking to migrate an entire server with several databases which collectively contain every user in the company. Obviously, I would prefer to directly migrate the logins without having to make everyone reset their own passwords. Anyone know of a way to do this?
I tried dts'ing the syslogins table but it fails due to password collumn being read only.
In a recent Disaster Recovery TEST, I successfully recovered some SQL Server 6.5 databases by re-installing SQL Server, applying the SP5a, restoring the application database, and then BCPing IN the syslogins. Everything went normal, as I have done this many times.
I had one SQL 7 to recover. I used the same procedures, however, the BCP in did not work. I ended up getting a Dr. Watson message (not sure if it was related to the BCP or not). Luckily I had the Logins scripted and was able to apply them as follows:
USE master
go
EXEC sp_addlogin 'user1',
0x21312144332c534025532f3a442d5e4c,
@defdb = 'DBNAME',
@deflanguage = 'us_english',
@sid = 0xf8ddfa809a05d411a56600508ba705cb,
@encryptopt = 'skip_encryption'
go
This seemed to work, and luckily there weren't many users in this database.
How do you get the syslogins in for SQL 7?
Thanks,
Carl
hi,
I created a new 6.5 sql server. I brought all the logins from the old
server. I bcp out the syslogins from the old server and bcp into the new
server. There were no errors from the bcp in. Somehow, I still can't
login to the new server using the same password from the old server.
Why?
how can I fix it?
thanks
my problem is : I want to export syslogins from database SQL6.5 to syslogins SQL7.0.
I use BCP (command : mssqlBCP master..syslogins out d:empsyslogins.dat -c -Sservername -Usa -P -ed:emplogins.err)
It runs well.
AFTER to export to SQL7 database :
(command : mssql7BCP master..syslogins in d:empsyslogins.dat -c -SNEWservername -Usa -P -ed:emplogins.err)
I always have DR WATSON.
If someone has encountered this problem and solved it.
Your help will be strongly appreciate.
Patrick
Is it possible to have a two way replication of the Logins and Users between two servers? What we have is two SQL 7.0 servers, for an CRM package. They use the CRM replication program to move data back and forth. However, we need to keep the logins and passwords between the two machines ther same. Any thoughts on how to do this?
View 1 Replies View RelatedJust installed SQL Server 2000 Beta 2. I have a procedure that selects the suid from the syslogins table from a SQL 7.0 version that is now saying that column is not valid.
All the documentation I can find still refers to that being a valid column.
Any suggestions?
How can you manually update the syslogins table in SQL 2000? It keeps giving me the following error:
Server: Msg 4406, Level 16, State 2, Line 1
Update or insert of view or function 'master..syslogins' failed because it contains a derived or constant field.
I used to be able to do it in 6.5.
Pls help me out with this query as
select * from master..syslogins WHERE hasaccess = 1
as it is both giving me record where hasaccess = 0 and 1
brgds
Does anyone have a script already which pulls all the logins and what databases they have public/dbo access to? I'm looking for something more than just pulling syslogins/users and their default dbname, I need a list of all the databases they have dbo access to.
Thanks, Jim
I've got a tool that accesses syslogins to pick up some information.When I run the tool, I get the error message that sasys that my logindoes not have sufficient permissions to read syslogins. If I run undermy admin ID, everything is fine. HOWEVER, the intention is that thistool will be used by non-DBA staff members so we have a generic idcreated to run this tool specifically (non-DBA account).OK, so I go to Enterprise Manager and open up the master database, goto syslogins and add my non-DBA id to the permissions list as havingSELECT access, and click on EXECUTE.BOOM! No sign of my permissions being set.Am I missing something? If so, what do I need to do to set permissionsagainst this view?
View 1 Replies View RelatedHelp!!!!!!
I cannot get the SQL Server Upgrade Wizard to work!!
I have one machine with NT 4 (SP4) and SQL 6.5 and another machine with NT 4 (SP4) and SQL 7.0 (new install, never had SQL 6.5 one it). I want to upgrade a database to 7.0.
Each machine can 'see' the other via shares and even trying to register a SQL database in each other machines Enterprize manager (although that fails because they are different versions of SQL)
Using the Upgrade Wizard, on the third screen, where you specify the Server Name for the export server, it even has the other server's name in the dropdown list {I never even put it in}. And yet, after I set the export server name and put in the database SA account password and then put in the SA account password for the local server (the one that is running the Upgrade Wizard).
After I select next, I get a message that states...
'Unable to connect to Export Server. Please verify that you are an NT administrator on that machine.'
These are standalone servers. Not BDC's. I am logged into each machine as the administrator.
I have tried adjusting the SQL services to allow logging as the System Account and the administrator account and a whole bunch of options....
but I can't get it to work.
HELP!!! This shouldn't be this hard.... should it???
Does creating the tables first before copying the data from 6.5 to 7.0 is the best way to migrate from SQL 6.5 to 7.0?
Thanks in Advance!
I run SQL 6.5 on an NT 4.0 platform in a client/server environment with Windows 95 as the clients. I use VB 6.0 as my user interface with Access 97 utilizing DAO (yes, still DAO) for the ODBC connection to the SQL server. When I install SQL 7.0 and move my data to it, can I refresh my links in Access 97 from ODBC to OLE DB and automatically take advantage of the new connection, or do I have to rewrite my queries to take use OLE DB rather than DAO? Thanks for any suggestions and help.
View 2 Replies View RelatedHello People,
I currently have a MS Access database that will eventually be migrated to MS SQL Server. There won't be an application now, just the database and the reports. My doubts are on how easy this transition will be.
1 - After I build all the database with tables and relationships, is there a tool that helps me to import data?
2 - Is there a way to insert the data on the database other than writing the SQL codes, I mean maybe creating some 'forms'?
3 - Once the data is in, it there a way for me to create report on the SQL Server itself or do I need a report generator like Cristal Reports for example;
4 - If yes, then how easy it is to integrate them;
5 - Is it complicated to generate these reports or it is pretty much the same interface as in Access or Delphi?
Thanks a lot
Atha
I have an ms sql 2000 db which I am migrating from one shared environment to another. How specifcally can I do this?
I have a copy of enterprise but not a full version of SQL on my local desktop.
The hosting company from which I am moving has placed the db as a zip file on the root of my site via ftp. I have unzipped it and it appears as a backup copy (.bak).
I appreciate any detail steps, I assume via enterprise, that anyone can give me. Thank you.
I am very new to SQL so please bear with me. I have an SQL 2000 server running on Windows 2000 server in an NT4.0 Domain. We are planning to migrate (not upgrade!) everything to a new WIndows 2003 AD environment using ADMT. I wanted to know if there are any issues with the migration of an SQL server if we are to move forward with this strategy. I have heard that an SQL database keeps domain related information within its database and wanted to know if this is true.
Thank you
Hi,
I need to migrate db,Im planning to use attach & detach option.Is there any cons in migrating the databases(32 bit,not clustered ,sql 2005) to databases(64 bit,clustered,sql 2005).What are the things do i need to take care of?what are the things do i need to do after the migration?
Thank
I've developed a website and it's hosted by a hosting company. Lately i've registered a new domain with a new virtual hosting account. I've transfered my web files succesfully. But migrating the sql server failed for a couple of times now. My database containes many tables with many products. My hosting provides doesn't give me the opportuniy to restore my backup which i've backuped from my old server. First I tried to use the import and export tool of sql server 2005. This was very easy and it succeeded. But this tool only migrates the tables and the views. All the common stored procedures of asp.net membership are not exported. So I can't login at the moment. I've tried to generate a sql script with all the stored procedures with sql server management studio. But If i want to run this script on my new db, it fails with various errors. I tried to use the aspnet_regsql tool to create all the membership tables and stored procedures. If this succeeds, I can import and export my old db to the new one. But i'm getting a error while trying to use the aspnet_regsql tool. The error number is 290 and the error is: How can I fix this? Er is een fout opgetreden bij het uitvoeren van het SQL-bestand InstallMembership.sql. Het SQL-foutnummer is 290 en het SqlException-bericht is: Invalid EXECUTE statement using object "Relation", method "SetUseVarDecimal".----------------------------------------Details van fout----------------------------------------SQL Server: Database: [samet]Geladen SQL-bestand:InstallMembership.sqlOpdrachten mislukt:/*************************************************************//*************************************************************//*************************************************************/DECLARE @ver intDECLARE @version nchar(100)DECLARE @dot intDECLARE @hyphen intDECLARE @SqlToExec nchar(400)SELECT @ver = 8SELECT @version = @@VersionSELECT @hyphen = CHARINDEX(N' - ', @version)IF (NOT(@hyphen IS NULL) AND @hyphen > 0)BEGIN SELECT @hyphen = @hyphen + 3 SELECT @dot = CHARINDEX(N'.', @version, @hyphen) IF (NOT(@dot IS NULL) AND @dot > @hyphen) BEGIN SELECT @version = SUBSTRING(@version, @hyphen, @dot - @hyphen) SELECT @ver = CONVERT(int, @version) ENDEND/*************************************************************/IF (@ver >= 8) EXEC sp_tableoption N'aspnet_Membership', 'text in row', 3000/*************************************************************//*************************************************************/IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_Membership_CreateUser') AND (type = 'P')))DROP PROCEDURE dbo.aspnet_Membership_CreateUserSQL-uitzondering:System.Data.SqlClient.SqlException: Invalid EXECUTE statement using object "Relation", method "SetUseVarDecimal". bij System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) bij System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) bij System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) bij System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) bij System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) bij System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) bij System.Data.SqlClient.SqlCommand.ExecuteNonQuery() bij System.Web.Management.SqlServices.ExecuteFile(String file, String server, String database, String dbFileName, SqlConnection connection, Boolean sessionState, Boolean isInstall, SessionStateType sessionStatetype)
View 2 Replies View RelatedHello!
We are planning migrating to different server.
I know the answers how to migrate all jobs and DTS packages.
But the only question I have is "How to migrate logins and passwords?"
What is the best way to transfer logins?
Can't you just export syslogins table into text file and then import it into the new server?
Thank you
Lena
Has anybody been successful migrating userids from one server to another server using SQL7.0? If so, is anyone willing to please provide any sample scripts.
View 1 Replies View RelatedHI all,
Could any one pls suggest me, how i have to migrate to sql server 7.0 from db2. Pls also recommend some sites where will i get the information regarding this migration.
Thank you all.
---Chowdary
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?
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.
This is long post but I really want some feedback on this urgently so please hang in there!
I am migrating a SQL Server over to a new server.
I have done the following and although everything appears to be working like a charm I would like to know if there are any gotchas that I have not considered.
These are the steps I used to 'move' the master db.
/*
I restored all the users databases onto the new sesrver from backups of the old (file copies and attaches would also do).
I restored a db named masterbak from a backup of the master on the old server. And again into a second db called masterbak2. I then detach masterbak2. I need to use those files later on.
So at this point I now have all the user databases that the old server had but I dont have logins, extended stored proc defs, and any user objects that were created in the old master.
Now I want to put all those missing things into the new master db. These are the steps that I used to achieve this.
*/
-- where doing open heart on sys tables so need this turned on
EXEC sp_configure 'allow updates' , 1
reconfigure WITH OVERRIDE
-- remove all database defs from the copy of the old master
delete masterbak..sysdatabases
-- and load it with all the database defs from the new master
INSERT INTO [masterbak].[dbo].[sysdatabases]([name], [dbid], [sid], [mode], [status], [status2], [crdate], [reserved], [category], [cmptlevel], [filename])
SELECT [name], [dbid], [sid], [mode], [status], [status2], [crdate], [reserved], [category], [cmptlevel], [filename]
FROM [master].[dbo].[sysdatabases]
-- SYSSERVERS
-- remove all the servers defined in the copy of the old master
delete masterbak..sysservers
-- replacing them with those that are in the new master
INSERT INTO [masterbak].[dbo].[sysservers]([srvid], [srvstatus], [srvname], [srvproduct], [providername], [datasource], [location], [providerstring], [schemadate], [topologyx], [topologyy], [catalog], [srvcollation], [connecttimeout], [querytimeout])
SELECT [srvid], [srvstatus], [srvname], [srvproduct], [providername], [datasource], [location], [providerstring], [schemadate], [topologyx], [topologyy], [catalog], [srvcollation], [connecttimeout], [querytimeout]FROM [master].[dbo].[sysservers]
-- sysfiles1
-- remove the old
delete masterbak..sysfiles1
-- replace with new
insert masterbak..sysfiles1
select * From sysfiles1
-- sysdevices
-- remove the old
delete masterbak..sysdevices
-- replace with new
insert masterbak..sysdevices
select * From sysdevices
-- sysconfigures
-- remove the old
delete masterbak..sysconfigures
-- replace with new
insert masterbak..sysconfigures
select * From sysconfigures
-- sysaltfiles
-- remove the old
delete masterbak..sysaltfiles
-- replace with new
insert masterbak..sysaltfiles
select * From sysaltfiles
-- were done
-- turn off sys updates
EXEC sp_configure 'allow updates' , 0
reconfigure WITH OVERRIDE
/*
this leaves
syslogins intact
sysmessages intact
sysoledbusers intact
sysremotelogins intact (no records in mine anyway)
all other system tables are left intact as we want to import all the objects into the new server
*/
/*
now stop the SQL server instance;
rename the physical files master.mdf and mastlog.ldf to masterORIG.mdf and mastlogOrig.ldf
rename masterbak.mdf and mastlogbak.ldf to master.mdf and mastlog.ldf.
rename masterbak2.mdf and mastlogbak2.ldf to masterbak.mdf and mastlogbak.ldf.
I wanted to preserve the new master files so didnt rename to masterbak but I suppose you could.
Start up SQL server and everything should be fine.
Configuration settings are as expected, databases started up ok and file locations are ok, logins both SQL and NT are ok, all master user objects are present.
User and login SIDs match.
A linked server access that was setup failed but after redoing security on the linked server it was ok? Sysoledbusers holds the security for this but not sure about this one.
Important:
You need to check sql log for any errors.
It should be clean.
If you have auto start procs that reference reg keys that are not on the new server you with be told which key is missing and you can export key from old server and import into new server.
Dlls of course will need to come accross but again error messages will indicate the dll that is missing and its path.
So we now have a server that starts up clean but its of a different name of the original of course and since we have a myarid of external servers etc that depend on the server name we are going to keep the old name as well.
So now we change the server name, requires reboot, start up QA and logging into to each 'new' instance name, run the following
-- 'SQLSERV02 -> SQLSERV01'
sp_helpserver
sp_dropserver 'SQLSERV02'
sp_addserver 'SQLSERV01', local
and that should be that.
you might get this :
-- Server: Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 44
-- There are still remote logins for the server 'SQLSERV02'.
sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
-- have a look at it
select srvid from master.dbo.sysservers where srvname = 'SQLSERV02'
select * from master.dbo.sysxlogins
where srvid = 0 and
not(ishqoutmap = 1 and
xstatus&192 = 192 and
sid is null and
name is null and
password is null)
-- get rid of it
delete master.dbo.sysxlogins
where srvid = 0 and
not(ishqoutmap = 1 and
xstatus&192 = 192 and
sid is null and
name is null and
password is null)
sp_configure 'allow updates',0
RECONFIGURE WITH OVERRIDE
My last step is to get all those jobs and log shipping over to the new server. And I have not yet done this so am not sure if any further open heart is required.
But the theory is that I just need to restore an msdb taken from the old server.
Naturally you will need to put in place the same file path structure on the new server for those scripts / jobs that create files on disk - such as backup files.
I will find out about msdb soon enough as this is going down tonight.
Can anyone see any obvious gotchas in this method?
cheers and thanks for your feedback
*/
I am migrating my database from Access to MS SQL. I successfully migrated most of the Tables but I am going through a hellish time with queries/views
Can somwone tell me how to convert this query from Access to MS SQL:
-------------------------------------------------------------------------------------------------------------------
SELECT Manage_Pre_Award.A_ID, Manage_Pre_Award.Event_Name, Manage_Pre_Award.Plan_Date, Manage_Pre_Award.Actual_date, Manage_Pre_Award.Rev_Date, IIf([actual_date] Is Not Null,[actual_date],IIf([rev_date] Is Not Null,[rev_date],IIf([plan_date] Is Not Null,[plan_date],Null))) AS workdate,
FROM Manage_Pre_Award
WHERE (((Manage_Pre_Award.Rev_Date) Is Not Null)) OR (((Manage_Pre_Award.Actual_date) Is Not Null)) OR (((Manage_Pre_Award.Plan_Date) Is Not Null));
-----------------------------------------------------------------------------------------------------------------------
I have already tried this but get:
The Query Designer does not support the CASE SQL construct.
SELECT Manage_Pre_Award.A_ID, Manage_Pre_Award.Event_Name, Manage_Pre_Award.Plan_Date, Manage_Pre_Award.Actual_date,
Manage_Pre_Award.Rev_Date,
CASE WORKDATE
WHEN [actual_date] IS NOT NULL THEN [actual_date]
WHEN [actual_date] IS NULL AND [rev_date] IS NOT NULL THEN [rev_date] ] ELSE [plan_date]
END AS WORKDATE
FROM Manage_Pre_Award
WHERE (((Manage_Pre_Award.Rev_Date) IS NOT NULL)) OR
(((Manage_Pre_Award.Actual_date) IS NOT NULL))OR (((Manage_Pre_Award.Plan_Date) IS NOT NULL));
Hello, all. I need to migrate a visual Foxpro database to SQL server. Is there a way I can import the visual Foxpro data files into Enterprise manager?
Thanks in advance.
I have big list of jobs on MSX Box - A. Most of those jobs has multiple target server on it. I have to migrate those jobs to new box (new hardware platform). The goal is to migrate everything over to new and decommision old one. If I generate scripts "with Target Server" option, can I run that script on to the new box while both are on-line with two different server name? In other words, can one slave server be a target to two different MSX machine?
This is very urgent for me to get it done, as our old machine is dying and all the jobs are very business critical. Please respond as soon as you can.
Thanks
Hello All ~
I have to migrate a database from 6.5 to 7.0 server. We don't have an upgrade wizard to i will have to do a turn around like :
> Scripting the objects
> Running the scripts in 7.0
> BCP out the data into 7.0.
But the problem lies here ! how do i BCP the whole database table data in one shot ? bcos i have a lot of tables in the database and i cannot afford to do a table at a time. If anyone has any script which creates pl do pass it on to me.
One issue that we are facing while migrating our dts packages from development to test is that in all our DTS package tasks, every object used is prefixed with the database name. For eg. in a Data Transformation task the Destination object has the prefix dw_dev.dbo.dw_user. This is causing problems as our database name in development and test is different and our packages are not running.
I understand that one can change this property in the Set Connection Task by changing the Source/Destination Object property to the new databse name and running the task. However that would involve changing the source and destination names in all the data pump tasks in all packages.
Is there an easier way to do this?
Thanks in advance
Is there anyway to copy all the DTS packages and jobs in one step from one server to another. I usvally copy one DTS package at a time. As our packages count grown bigger, it became a painful process.
If anyone knows a way to copy these packages and jobs in one then it would be very helpful.
Thanks
Can anyone tell me how I can migrate database from one server to another I need to migrate all the tables, stored procedures, functions and so on thank you
View 3 Replies View Relatedi have computer with windows 2000 an sql 7.0 (computerA)
goal is windows 2003 with sql 2005
I see my choices as is:
A) upgrade win2k to 2k3 then upgrade sql7 on w2k3 to sql2k5 on win2k3.
(i see many potential problems with that)
B) have computerB with clean install of win2k3 and install sql2k5 on it.
if this is the case, what is the best / easiest way to get the database from 7 to 2k5? Database copy wizard? dts? attaching the db?
Anything else I should lookout for ?
thnx,
vext
I need to migrate dts packages on one server to structured storage files and then restore them all to another server in a different network
i got the restore method down fine, but all of the backup methods I used yield an incorrect DTS package. the only way i can get the package to look right is if I manually open and save it
i would like an automated way if possible
i tried tools from sqldts.com, wrote my own, etc... none of them work right
it is sql server 2000
thanks