I have an application that runs off an sql server 2000 sp3 database. I have taken a backup (.bak file) of the production database and restored it to another server for testing purposes. the users cannot login to the new server because the logins have not been transfered to the new server. I am looking to transfer the logins using the procedure outlined at the following link: http://support.microsoft.com/kb/246133/ under the header "Create and Run Stored Procedures in the Master Database". what I need to know is will this procedure affect the production server in any way? (I can't have that) and is there anything else I need to do after this to let the users login to the test server with the same name and passwords?
Is there a utility to transfer the logins for a database for one server to another without setting the login passwords to null as the SQL transfer utility does?
I would move a Database to another server. I try to use DTS but I have problems with this process because DB have big tables, I think. I try to use DETACH and ATTACH procedures but logins doesn't export. And more, in new server there are already logins from another DBs.
What's the best way to solve this problem? Please, help Thanks
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.)
ok, first, I know... I forgot to run a backup of the master database, and I forgot to run a script to caputure logins. Not that that is out of the way... I need to recreate the logins under the Securities tab below the databases. All the company databases have the user names and passwords assigned to them, but they are not able to login, because they are not able to authenticate to the SQL server first.
Is there a script that someone has that will copy the company database security info for the users and recreate them in the SQL security tab?
I know that I can rebuild them manually, but I need to delete them first in the application software, then delete them from the databases, and then recreate them in the application software... and as simple as that sounds... it is a slow moving process.
Hi I need to copy all the jobs from one server to another server. How i have to do. I tried to take script of MSDB database, but it is not scripting all the jobs. PLs anyone suggest me what i have to do with scheduled jobs in 65. it is very urgent pls. thank u --Rk
I've read about using starsql odbc and sql server 7.0 DTS utility to transfer the database or data from a DB2 to SQL Server. I have a SQL Server 6.5 and need to transfer data from a DB2. Is the operation the same? Is there a utility in sql server 6.5 that allows this?
i ave transfered all the users from one of my sql server to another sql server but what the problem is that all the users that are on new sql server having a NULL password.....
so that means that when u transfer users from one sql servers to another the passwords will be remain as null for all users......
Has anyone had a problem transfering a database to a server. This is the first time I have done this. I have a site and sqldatabase hosted by a hosting company and I am supposed to transfer my database This is what I'm doing
From the Start Menu, select "Programs" -> "Microsoft SQL Server" -> "Import and Export Data" then click "Next". In the "Server" field, type in the Name or IP of the source server (your server), choose "Use SQL Server Authentication," type in your username and password, choose your database from the "Database" field, click "Next". Note: You can also use Windows Authentication if the SQL server is a local server. In the "Server" field, type in the hostname of the DiscountASP SQL Server (this is listed in your hosting control panel), choose "Use SQL Server Authentication," type in your DiscountASP database username and password (this was sent in your database order confirmation email), choose your database from the "Database" field, click "Next". Choose "Copy objects…", click "Next". Uncheck Use Default Option. Click Options, uncheck "Copy Database users and database roles", "Copy SQL Server logins", "Copy object-level Permissions". Leave next window as is, click "Next". Click "Finish".
It seems to work ok untill it gets to about 90% done then a message says "There has been an error" and gives no more info. Can anyone help me out
We have transfered some jobs to new server using DTS 'export jobs' task, but they are failing when trying to execute DTS packages in them saying 'login failed for user----though everything is correct with the user!
I need to transfer seven tables from Informix DB to SQL Server every night. Any suggestions for the best way of doing it? Is DTS the best way? How about through replication?
How can I make a connection in SSIS Data Flow to connect to DB2 on the mainfrain? Have several DTS packages in SQL Server 2000 that uses the ODBC connection.
I have managed to transfer the SQL SERVER 2000 database tables to access database by using DTS. But how can I transfer stored procedures in SQL SERVER to Access 2000
Hi I am trying to develop a web based application. I am trying to insert data from my web form into sql server database using stored procedures. I am having a problem while i try to exceute the command. The error given by system is System.InvalidCastException: Object must implement IConvertible. at System.Data.SqlClient.SqlCommand.ExecuteNonQueryany suggestion what am i missing?With Regards,Sameer Jindal
Hello folks,Are there any tools that can transfer database and it's content fromSQL Server Standard version to SQL Server Express database?I tried to backup database in SQL Server Standard and then restore itinto SQL Server Express but it didn't work. Are there any tools thatcan extract SQL clauses to create database, tablesand data inserts into file? Or any other method to transfer database/data from SQL Server Standard to SQL Server Express?Cheers,
I am writing program in VC++ through SQl-DMO calls.My problem is when i when i tranfer(import) a text file(comma seperated) into SQl server through a SQl-DMO method called ImportData which is a method of Bulk copy object.Its is not able to convert the data field in the text file to corresponding value datetime in SQl server whereas other data types are working perfectly.
This is the record i need to convert:
90,MichaelB,Wintriss,Inspection,Paper,11,Job101,1, {ts '2000-12-10 15:54:56.000'},D:public233 and 247233.mcs,
There are about 1000 records like this .the text file is generated by SQl when i export data from Sql server tables.This file has lot of records.Now i need to put the record in the text file into SQl server tables.During which when i pass the text file it gives problem in converting date and time value.I cannot remove the bracket and ts as ,{ts '2000-12-10 15:54:56.000'} it generated by SQl server tables
and this is the date field {ts '2000-12-10 15:54:56.000'}
Whereas if i export a table in SQl server in Binary mode and then import the file back it works but when do it as text it gives the above error
Pls help me in this i would be very thankful to you.
I have a oracle database that creates a table (to store call records) everyday and places data into it. How can i use Interation services to transfer the newly created tables to an SQL server database (SQL server 2005).
can anyone help me get around the following error I am trying to transfer a database from one sql 7 server to another but keep recieving this problem when I attempt a restore of the backup file. Is anyone able to give us any advice tips etc. Any other ways to transfer the db would be good as well!thanks guys! "The database you are attempting to restore was backed up under a different sort order ID(51) than the one currently running on this server (52), and at least one of them is non binary sort order. Backup or restore operation terminating abnormally."
Does any1 know how to prevent a login screen being encountered upon hitting the SQL dbase? I'm sure there is a table in SQL server that you can create that allows this (and the number of rows retrieved as well). Can any1 help me out?
I have succeeded in reattaching the db to the new server, but the old logins do not work, as has been experienced by many people.
What follows was is a solution from an earlier post (last year) on this topic. I tried running the queries from Master in on the new server, and got the following error: Server: Msg 2601, Level 14, State 3, Line 1 Cannot insert duplicate key row in object 'sysusers' with unique index 'sysusers'. The statement has been terminated.
The sql queries I am using are:
insert sysxlogins(srvid, sid, xstatus, xdate1, xdate2, name, password, dbid, language)select srvid, sid, xstatus, xdate1, xdate2, name, password, dbid, language from "oldServerName".master.dbo.sysxlogins where name not in ('DISTRIBUTOR_ADMIN', 'REPL_PUBLISHER', 'REPL_SUBSCRIBER', 'TRACEUSER', 'SA')
AND
insert sysusers(uid, status, name, sid, roles, createdate, updatedate, altuid, password) select uid, status, name, sid, roles, createdate, updatedate, altuid, password from "oldServerName".master.dbo.sysusers where name not in ('public', 'INFORMATION_SCHEMA', 'db_owner', 'db_accessadmin', 'db_securityadmin', 'db_ddladmin', 'db_backupoperator', 'db_datareader','db_datawriter', 'db_denydatareader', 'db_denydatawriter', 'guest', 'dbo')
The old server is set up as a linked server on the new server.
This suggests that the information is all in the new server. Any tips? TIA D. Lewis
We are in process of migrating of databases from one Server to another, Can anyone please suggest me how to transfer or copy the logins from one Server to the other instead of creating them all over again??
I need to restore the logins from prod server "A" to a dev server. The dev server has many databases from different prod servers. If I restore the master from prod A to dev, i'll lose the logins on dev for the other dbs. How can I get the logins from prod A to dev without losing the other logins for the other dbs?
I need to create a new login that only has access to 2 Views.
I have tried everything, A New Role, Schema, set the Securables but when I connect to the server with MS Access or MS Excel, and sign in using that Login, I can still a bunch of tables. I only want this Login to be able to run either view and not see anything thing else.
Historically, many of us have used bulk copy to move selected contents of syslogins from one server to another so that the logins stayed in sync for things such as replication, log shipping, etc.
In SQL 2005, I have no access to passwords (this is proper from a security perspective) so I can't generate a file with bulk copy. I also can't update sys.server_principals, so it doesn't really matter that I can't export the passwords.
So what is the SQL 2005 solution? Suppose I decide to mirror my database. How do I get the logins that are users of that database over to the mirror server? Scripting the logins won't work because it generates a random password. sp_change_users_login requires me to provide a password but then I would have to know each login's current password or give them each a new password. This is viable with 2 users; it is not viable with 2000!
I know that Copy Database Wizard will move the logins with the passwords intact, but it will assign new SIDs to SQL Server authentication logins. That's fine as long as it is a one-time move but not if I am maintaining a warm standby.
This problem has been one of the most frequent problems posted on newsgroups, etc. in the past. But we have had solutions. I fear that we do not have one in SQL 2005.
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?
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.
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?
I am developing an application in VB.NET that is using a sql database. I am trying to start testing with multiple computers running the app while the database is on a remote computer. I have 2 desktop computers networked right now with 1 of them having the database and the other I want to run the program on. I am trying to figure out how to use windows authentication to login to sql server from the remote computer. I can login using Sql authentication but it is taking forever and I keep getting timeout errors so I wanted to try Windows Authentication to see if it is faster. If someone could walk me through the steps I need to take or give me some suggestions to fix my problems I would greatly appreciate it.
