Login Mapping Lost After &"Copy SQL Server Objects&"
May 6, 2008
After I run a SSIS package which is a "Trasfer SQL Server Objects Task", the database users has lost all mapping to SQL server login. I've tried various options inside the package, however, to my surprise, sometimes the user settings at SOURCE database is lost! I won't suppose to change anything in the SOURCE database after running a data transfer... Anyone knows what's wrong?
Thanks in advance!
I use linked serve with (Security :be made with specific security conterxt) to connect to my production server from a Data Archive server.
Stored Procedures loop throught the prodcution data to insert records into Archive tables...Howerver after few hundreds of iteration I get the following error...
"Access to the remote server is denied because no login-mapping exists."
I am confused with the error message as it happens only after some successful insertions....
Hi Folks SQL2k on NT4 Domain I have a UserDB on SvrA - Access to UserDB is Via a DomainGroup and is assigned db_owner role UserDB Executes a local SP which in turn Executes a remote SP on SvrB via a Linked Server The Linked Server Login Security is via mapping to a remote account which has db_owner on the remote DB. I would like to. a) stop ALL users on SvrA from being able to use this linked serverb) tightly restrict permissions of the remote account to the remote db Is it possible to MAP above local server login to remote server login as the local login is via a domain group. I cannot significantly alter UserDB current Login Security I'm also worried that another system/db is utilizing this Link (legally) but I'm not allowed on the production box to monitor it (Hhrummphh - but unfortunately I have the job of providing scripts to tighten security :rolleyes: - hence the prefference for mapping local to remote users I'm sorry I'm not well versed in SQL Security & this is only a central part of my bigger security job(involving horrible RPC's, dynamic SQL, heteregeounous joins, double hops, delegation etc) Any help appreciated GW
I’m trying to create a DTS Package to copy my sql Server objects to a test Server. The server I’m copying from is UMTS1 and the server I’m copying to is UMTSDEV. The database name is ProgramSpecs and exists on bother servers. My login is assigned to all server roles on both servers. I have created databases on both servers manually so I’m pretty sure I have all the necessary permissions. I’m using the DTS task ‘Copy Sql Server Objects’ to copy sql server objects and have selected “Drop Destination objects first”.
When I try to execute the package I get the following error: Error source: MS SQL DMO Error Description: Invalid OLEVERB Structure [SQL DMO] create file error or UMTS1.ProgramSpecs.LOG
My goal is need to synch couple of tables in QA and Prod. I used "Copy SQL Server Objects Task" in DTS package with copy option "replace existing data". It is trying to truncate and insert new data. But BOL is says "Overwrite existing data in the destination objects with the new data from the specified source". Let me know your thought about this.
I am trying to use a linked server and it works as long as I do not specify the sp_addlinkedserver @provstr parameter. If I specify that parameter I always get a 7416 "Access to the remote server is denied because no login-mapping exists" error. I have tried adding the logins various ways but it's very specific to the @provstr parameter, and it doesn't even matter what I put in that parameter. As soon as I put something in there whether it is valid or invalid, I get the error.
Anyone else seen this? There is an amazing lack of any discussion about the error when I search for it.
It doesn't even make any difference what I put in the @provstr parameter - the sp_addlinkedserver statement always executes without an error, but running a query that uses the linked server generates the error.
Copy objects and data between SQL Server databases " Display the Select Objects to Transfer dialog box, where you can specify both objects and data to copy, if both the data source and destination are Microsoft® SQL Server™ databases. The objects you can transfer include tables, views, stored procedures, defaults, rules, constraints, user-defined data types, logins, users, roles, and indexes. You can transfer objects only between multiple instances of SQL Server version 7.0, from an instance of SQL Server 7.0 to an instance of SQL Server 2000, and between multiple instances of SQL Server 2000. "
can I apply "Copy objects and data between SQL Server databases" to run in two different sqlserver 2000 ( not an instance ) . what I mean is I have two different sql servers located in two different locations( I am not using an instance installation) can I still run the copy and get an identical database in both servers. Q2. if I have two sql server 2000 with different collations (one is binary and the other is the default) will I be able to run the copy wizard and still have an identical copy of sql server in both servers.
I personally tried to run the copy wizard and IT NEVER WORKED FOR ME and I really do not know the reason.
I'm a wee bit of a newbie concerning DTS and have inherited a db with a DTS containing a Copy SQL Server Objects task set to run nightly. Essentially, it does an informal backup of some core data.
Recently, I was notified that one of the tables it copies over is now empty on the Destination db. The DTS shows that it runs successfully with no errors logged, the table in question IS selected to be copied from the Source database, there IS data in the Source database table, and every other table in the Destination database is populated appropriately.
Any ideas on what would cause this one table to be empty without generating any errors?
I just upgraded to SQLServer 2005 Standard. I do development in adatabase on one server. When a solution is completely tested, I moveit to the operational database on a different server. With SQLServer2000, I could just open the .adp file and do File->Get ExternalData->Import to move the objects. That does not work with 2005.What is the recommended method for moving objects from one database toanother? Obviously, the objects that changed will not be the sameevery time.Thanks!Jer
I create a new SSIS package, drag in an Execute DTS 2000 Package Task, select and embed a DTS package which consists only one one task (as above), and then change the source & destination details (svr + user/pwd). Then when I go to the Copy tab, I get the following error when I hit Select Objects, to view the objects which the embedded DTS package should copy:
SQL-DMO error 21776: general error.
On further inspection, none of the objects selected for copy within the atomic/original DTS package, remain selected for copy within the embedded DTS package.
I have googled to search for an answer to this one, but to no avail. Any ideas would be greatly welcomed.
Can anyone refer me to good 'recipes' or sources of information on thistopic??I have Visual Studio Tools for Office, which installs SS 2005 Expresslocally to my XP box, and I want to develop in SS 2005, then copy thetables or queries or reports etc. to a SS 2005 Standard server.Thank you, Tom
I recently installed a trial version of Sql12k on my laptop. Since 1st of November I've been creating tables without problems (15) and different schemas.I would like to emphasize that both mdf and ldf files are allocated in my USB3 portable disk. is that a problems for Sql12K? SSMS had stuck just for 20 sec or so. Then, system itself kill the task. A huge surprise when I restarted again... all the databases including the last one.. without objects. I mean, user databases...
Where are my logins? Where are my tables and schemas? Jesus christ.Sql logs inform the following (in all the moment my USB hardisk was properly connected)
11/08/2015 16:00:30,spid19s,Unknown,The operating system returned error 21(El dispositivo no está listo.) to SQL Server during a read at offset 0x000000 00140000 in file 'f:enriccopia_de_bbbb.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency
I'm making a copy of some tanles between 2 servers.
Server 1 requires a sql login
Server 2 is using Windows Auth.
I have a user on server 1 named "odbc" able to log in.
however my copy task fails, when I drill the error, it's lists the first user in server 1 alphabetically as the failed login???? but in my dts I am specifying the "odbc" user and password.
I think I have a permissions problem on server 1. So my Question, what minimum permissions does user "odbc" need to copy a table?
On server 1 I can copy from northwind to server 2 just fine..but any other db on server 1 causes the weird failure with the wrong username.
We are having problems with the response times from UPS WorldShip after switching from SQL Server 2000 to 2005.
I think that the problem can be fixed from the database end by setting the permissions correctly for the user/role/schema that is being used by WorldShip to connect to the server but, I'm not sure how to do it.
The Setup
Client UPS WorldShip 8.0 running on XP Pro SP2 Connecting via Sql Native Client via SQL Server Login Connection is over a T1 via VPN
Server - SQL Server Standard Edition on Windows Server 2003 2x3ghz Xeon processors w/ 4gb ram
The user that is being used to connect runs under it's own schema and role and only needs access to two tables in a specific database on the server.
What UPS WorldShip seems to be doing is on a continual basis retrieving information about the layout of the database via calls such as the following
This seems to happen whenever WorldShip contacts the database to find out information in order to be able to create a mapping to the database as well as exporting information to it. Because of the VPN connection these calls take anywhere from 20 seconds to 3 minutes.
I am fairly confident that the problem lies with these calls to the database which I was able to capture using the SQL Server Profiler. We have experimented with the following setups.
1. Connecting to SQL 2000 over VPN with SQL Native Client - No noticeable lag 2. Connecting to SQL 2000 over VPN with SQL Server 2000 driver - No Noticable lag 3. Connecting to SQL 2005 locally with SQL Native Client - No Noticable lag 4. Connectiong to SQL 2005 over VPN with SQL Native Client - Lots of lag
Our network admin has been testing the network connections over the VPN and it is very responsive with none of the long wait times found when using UPS WorldShip.
Now for a possible solution other than getting UPS to fix their software. I think that by limiting the tables and views that the login is able to see will cut down significantly on the lag times that are being experienced. The problem is that there were 264 items that were being returned by sp_tables. I was able to cut that down to 154. I am unable to disable access to any of the rest of the items because they are server scoped.
Take for example the INFORMATION_SCHEMA.CHECK_CONSTRAINTS view. When I try to deny access to it in any way I get the following error:
Permissions on server scoped catalog views or system stored procedures or extended stored procedures can be granted only when the current database is master (Microsoft SQL Server, Error: 4629)
Am I able to deny access to these types of object and if so how? Also, what objects should be accessable such as sys.database_mirroring, sys.database_recovery_status, etc?
Using import task only can select and copy tables and views.
Since there are many objects at the same DB I don't need copy, is there any way let me select and copy some objects, such as functions, stored procedures?
how can i copy a data base created in sql server2000 to another server (sqlserver2000)without replication because they're distant and not connected i have tried the DTS but it does'nt copy stored proc ,views,triggers,diagram,... help please
I have an application that I wrote that is running in the local office and a remote office. The two offices are connected via a hardware VPN. The connection in the remote office is wireless and can give speeds down to 40kbps.
Each office is running MSDE 2000 and runs off of a separate database with a different name. I would like to have the database from the remote office available in the local office. It doesn't have to be completely current. A 24-hour delay would be fine. Since Transaction replication is not available in MSDE, I can use either Merge or Snapshot. Since the local office wants to allow folks to access the remote office's database without allowing them to affect the remote database (query purposes only), it seems that Snapshot is the way to go.
The database in the remote office is as follows: Data File - 50MB; Log File - 5MB. I don't expect this to grow very fast.
The question I have relates to performance over this slow link. Would I be better off using Snapshot replication or just creating a DTS package and having that run on a nightly basis to copy the database?
Also, with a DTS Package, if the job fails due to the link resetting (remember it is wireless), I would have to configure retries, etc. Would Snapshot replication automatically recognize this failure and try to run again?
The problem is this: In SQL 2000 DTS there was an option for "Copy Objects and Data Between SQL Servers". However, this option has been removed in SQL 2005 SSIS. Apparently the only way to do this in SQL 2005 is to create a .DTSX package in SQL Server Business Intelligence Developement Studio or VS 2005. You do this by creating a new Integration Service Project and using the Transfer SQL Objects Task. Within the properties of this task you can select any of the options that were available in the SQL 2000 DTS export wizard. I have set up a test package that will copy a stored procedure from one db to another but I am unable to get it to work. It runs fine but the result is that the SP is not copied.
I am new to Visual Studio and I think I probably just need help in know ing how to run a package in SQL Server Management Studio. I was able to import the package into SSIS in the Management Studio and run it without errors not with the expected result (the copy of an SP from one db to another). I'm sure there are people besides me who would like to have the ability to easily perform ad hoc copies of objects between SQL servers. If anyone has any experience with using a SSIS package to do this please help. Thanks!
I have recently moved from a Microsoft SQL Server 2000 to a SQL Server 2005.
In the good old Enterprise Manager, when I imported a table from a SQL server to another, I could choose "Copy objects and data between SQL Server databases". When choosen, all primary keys, and default values was copied.
My question is: Is it possible to do the same in Microsoft SQL Server Management Studio with SQL Server 2005? And how do I do it?
I have tried using the "SELECT * INTO NewTable FROM OldTable" statement, but it just drops all information about primary keys and default values.
I want to migrate from one machine to another is it wise to use SSIS "Copy Objects and Data Between SQL Servers" task to accomplish this....migration both data and metadata (in master,model,and msdb ) databases?
I have not used the copy objects wizard that much. I used it today to copy 4views from my dev box to production. It copied the views, but also wiped outmy data in all the tables that the views are built around!!! Inproduction!!!!Can someone provide me some insight into why this happened?Thanks,Chad
I am sure there will be a simple answer to this but it has got me stumped.
Having to move over to Vista with my new machine so I am having to switch to 2005 version for my development but still upload to a 2000 server.
I have had a look at 2005, like the new Management Studio, however I ahve a couple of problems which I can not find the answer to.
Firstly, the SQL Query Builder, where has it gone? I often have to import/export data from Excel files and used to use the SQL query builder to create my queries. If I want to copy all columns it is fine but if I want to import select columns I find it easier to view a list and then just add the ones I want.
Am I missing something here?
Secondly, copying stored procedures, before when running DTS ther were three options, Copy Tables/Views, Data Using Query and Copy Objects.
I used the copy opbjects a lot as it was a very quick way of transfering a group of tables and stored prcoedures that I had created. This appears to have now been replaced with Copy Database, which copies everthing, can can not be used to copy from SQL2005 to SQL2000.
If I want to copy multiple stored procedures from SQL2005 to SQL2000 how is it done now? I have tried finding out but have not been sucessful.
I have a SQL2000 database that I need to copy tables from to my SQL2005 database. The table in the 2000 database are owned by a login named tsreader. This login is also in the dbo database role. So, when I access tables I have to prefix tha table name with tsreader. Currently in a SQL2000 database I have DTS packages that pull data over from this database everynight and they work fine. However when I try to do this from SSIS, I get the error "Object does not exist". My assumption is that it is trying to access the database tables as a "select * from tablea" as opposed to "select * from tsreader.tablea".
Any thoughts on how to make this work? I know I have the login correct because it is the same one I use for the SQL 2000 packages. Also, in SSIS it allows me to chose the tables I want yet I still get "Table does not exist at source" when I try and execute it.
"Failed to copy objects from Microsoft SQL Server to Microsoft SQL Server "
I keep getting this when trying to copy stored procs from one db to another on the same server. I am using the DTS wizard. I have been able to copy the tables but I need the sp's too, and there are too many to copy one at a time.
How would I, using a sql script, copy permissions assigned to a useror a role in one or more databases to another user or a role in theirrespective databases?Help appreciated
Challenge: Datapumping. To copy daily production data from N x 100 SQL 2k servers to one central SQL2k5 server. Sometimes copying task might demand transferring schema objects like temp tables and procedures from sql2005 to sql2000.
Since system organisation is different, what would be the best approach ?
Hello all :) First, sorry for my english but I don't practice everday So, I'm a new user of Microsoft SQL 2005 and I'm testing some functionalities in SSIS.
I used the Transfer Objects Task to copy one database to another. The both db's are on the same instance.
When I execute the package, I got an error message, the logins are dropped. I cannot connect to the database engine, even with my admin account. I have to restore the master database !
So my question is : why this SSIS task drop all the logins in sql server in place of just copy one database??????
passing serialised objects to a stored procedure for the purpose of data inserts. I see this as being a way to handle multiple row inserts efficiently.
However, in my limited use of XML data I am not so sure how to link the data when I have a dependency on another "object" within the serialised XML.
Below is a code snippet showing what I have so far.
The first insert statement works fine - but how to retrieve the identifier created by the DB - I want to use an SQL statement that finds the record in the table based on the XML representation (of the PluginInfo), allowing me to insert the ConfigurationInfo with the correct reference to the PluginInfo
DECLARE @Config NVARCHAR(MAX) DECLARE @Handle AS INT DECLARE @TransactionCount AS INT SELECT @Config = ' <ConfigurationDirectory > <ConfigurationInfo groupKey="Notifications" sectionKey="App.Customization.PluginInfo"
I have a small problem in parameter mapping for Execute SQL Task. I am using a delete statement with 2 conditions. Followed by another Execute SQL Task which contains commit statement.
delete from tname where c1 = ? and c2 =?
where c1 is number(4) datatype and c2 is of varchar2(20) datatype in oracle.
The connection manager i am using is ORacle OLE DB provider. I am passing 2 global variables i.e g_v1 of Int32 and g_v2 of String Type.
In the parameter mapping of the Executing SQL task, i am mapping these 2 variables for c1 and c2 and changed the datatypes inside parameter mapping as Numeric for c1 and Varchar for c2.
I also set the property as ByPassPrepare = True.
When i am executing the package i getting INVALID NUMBER ERROR. i believe the SSIS is unable to perform the implict datatype converison.
For the next run, i changed the g_v1 varible datatype to Double and also i changed the parameter mapping for c1 as Doble datatype. This time it is working fine. I can see the Green signal for the 2 SQL Tasks.
But when i connected to Oracle check the count in the table, the data is not getting deleted.
Also, I set the property RetainSameConnection = TRUE for oracle connection manager. I am not able to trace this logical error.
The same is working fine in my local machine. But i am facing the problem when i deployed the same on the client machine.
Is there any problem with parameter mapping? What should be equialent Datatype for Oracle NUMBER datatype that should be used inside the SSIS package while declaring the global variable and inside the parameter mapping.
Guys, How can we copy login (existing login will replace password) in SQL 2005? In SQL 2000, i can copy the password hash then update the sysxlogin table. as that table doesn't exists in SQL 2005 anymore, and using the SSIS transfer login task might not do what i wanted as i need to copy most of the login but not all (i know we can select from the list, but its not dynamic). As you can guess, this is for copying production login to BCP login and keep them in sync, how do you guys do it for 2005 now?
p.s. Does anyone have any needles I can borrow? I think sticking them in my eyes would be nicer than working with SSIS.
===================================
An error occurred while objects were being copied. SSIS Designer could not serialize the SSIS runtime objects. (Microsoft Visual Studio)
===================================
Could not copy object 'Preparation SQL Task' to the clipboard. (Microsoft.DataTransformationServices.Design)
------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.762&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=SerializeComponentsFailed&LinkId=20476
------------------------------ Program Location:
at Microsoft.DataTransformationServices.Design.DtsClipboardCommandHelper.SerializeRuntimeObjects(ICollection logicalObjects) at Microsoft.DataTransformationServices.Design.ControlFlowClipboardCommandHelper.InternalMenuCopy(MenuCommand sender, CommandHandlingArgs args)
===================================
Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)
------------------------------ Program Location:
at Microsoft.SqlServer.Dts.Runtime.PersistImpl.SaveToXML(XmlDocument& doc, XmlNode node, IDTSEvents events) at Microsoft.SqlServer.Dts.Runtime.DtsContainer.SaveToXML(XmlDocument& doc, XmlNode node, IDTSEvents events) at Microsoft.DataTransformationServices.Design.DtsClipboardCommandHelper.SerializeRuntimeObjects(ICollection logicalObjects)
