Change In Database User Perms When Copying Database
How to maintain Database User Permissions when copying the Database from One SQL Server to another(Either through backups or sp_detach). The reason is the login sid is different in the target server and as a result the database user is not able to map to the login existing in the target server. The only way I can correct this is through dropping and recreating the user's again and assign the permissions, or change the system catalog - sysusers to remap the login to the user in the database.
I do not wish to use the sp_addalias as it is available only for backward compatibility.
Is there a better way of doing this ?
View Complete Forum Thread with Replies
Related Forum Messages:
Change Logins For Multiple Database User Groups
I have a SQL 2000 box with number of NT4 domain group logins. These groups are aliased to users in a particular database. I am now trying to map these users to new groups in an Active Directory domain. Is there an easy way to map the users to the new groups without deleting and recreating. I know this is possible with users but not sure with groups. Thanks, Michael
View Replies !
Copying A Table From Mysql Database To Sqllite Database
hi everyone !!i am selecting a table from the tblusers from mysql database.and i want that when my aspx page loads itself then the table tblusers gets copied into the sqllite database. for which i have to connect to both the databse.please suggest some idea and queries how can i do this? waiting for your replies Thanks in advance.
View Replies !
SSIS Error When Copying Database To Database
I am trying to copy one database to another using copy wizard for SQL Server 2005. The destination database is on another server/box. I get the following errors when executing the SSIS package: "The job failed. The Job was invoked by User abcd. The last step to run was step 1 (abcd_0_Step).". "Executed as user: BILLSVRSYSTEM. ...ion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:30:55 AM Progress: 2008-05-05 11:30:56.81 Source: crmtest_BILLSVR_Transfer Objects Task Task just started the execution.: 0% complete End Progress Error: 2008-05-05 11:30:57.34 Code: 0x00000000 Source: abcd_abcd_Transfer Objects Task Description: Failed to connect to server crmtest. StackTrace: at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect() at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.OpenConnection(Server& server, ServerProperty serverProp) InnerException-->Login failed for user 'abcdabcd$'. StackTrace: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.... The package execution fa... The step failed." Can this be done? Is there something that needs to be set security wise for this to work?
View Replies !
How Do I Change The Status On My Database? Currently Is Is Assigned As &"Restricted User&"
I restored a sql server database today. There is a problem though. In SQL server, right next to the name of the database are the words "Restricted User". Their seem to be some limitations on my access to this database now as I am not able to run some of my C# code against the database. How can I fix this in SQL server so that I am not a Restriced user. Example of what I am talking about. In SQL server my database is named Gorlaz. Just to the left of this is a yellow Icon that represents the database "Gorlaz". For illustration purposes I will use the character "$" to substitute for the yellow database icon. So this is how it reads in SQL server management studio. $Gorlaz(Restricted User)
View Replies !
NORTHWIND Database Was Re-created From A Different Database:How Can I Change The Entry In Sysdatabases For Database 'NORTHWIND'?
Hi all, From the http://msdn.microsoft.com/en-us/library/bb384469.aspx (Walkthrough: Creating Stored Procedures for the Northwind Customers Table, I copied the following sql code: --UpdateSPforNWcustomersTable.sql-- USE NORTHWIND GO IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SelectCustomers' AND user_name(uid) = 'dbo') DROP PROCEDURE dbo.[SelectCustomers] GO CREATE PROCEDURE dbo.[SelectCustomers] AS SET NOCOUNT ON; SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM dbo.Customers GO IF EXISTS (SELECT * FROM sysobjects WHERE name = 'InsertCustomers' AND user_name(uid) = 'dbo') DROP PROCEDURE dbo.InsertCustomers GO CREATE PROCEDURE dbo.InsertCustomers ( @CustomerID nchar(5), @CompanyName nvarchar(40), @ContactName nvarchar(30), @ContactTitle nvarchar(30), @Address nvarchar(60), @City nvarchar(15), @Region nvarchar(15), @PostalCode nvarchar(10), @Country nvarchar(15), @Phone nvarchar(24), @Fax nvarchar(24) ) AS SET NOCOUNT OFF; INSERT INTO [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]) VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax); SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID) GO IF EXISTS (SELECT * FROM sysobjects WHERE name = 'UpdateCustomers' AND user_name(uid) = 'dbo') DROP PROCEDURE dbo.UpdateCustomers GO CREATE PROCEDURE dbo.UpdateCustomers ( @CustomerID nchar(5), @CompanyName nvarchar(40), @ContactName nvarchar(30), @ContactTitle nvarchar(30), @Address nvarchar(60), @City nvarchar(15), @Region nvarchar(15), @PostalCode nvarchar(10), @Country nvarchar(15), @Phone nvarchar(24), @Fax nvarchar(24), @Original_CustomerID nchar(5) ) AS SET NOCOUNT OFF; UPDATE [dbo].[Customers] SET [CustomerID] = @CustomerID, [CompanyName] = @CompanyName, [ContactName] = @ContactName, [ContactTitle] = @ContactTitle, [Address] = @Address, [City] = @City, [Region] = @Region, [PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone, [Fax] = @Fax WHERE (([CustomerID] = @Original_CustomerID)); SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID) GO ==================================================================================== I executed the above code in my SQL Server Management Studio Express (SSMSE) and I got the following error messages: Msg 911, Level 16, State 1, Line 1 Could not locate entry in sysdatabases for database 'NORTHWIND'. No entry found with that name. Make sure that the name is entered correctly. =============================================================================================================== I know I recreated the NORTHWIND Database from a different Database before and I did not do anything for the entry in sysdatabases. How can I change the entry in sysdatabases for database 'NORTHWIND' now? Please help and advise. Thanks in advance, Scott Chang
View Replies !
Help-for Copying Database
hi, i m new 4 sql server. i create one app. in asp.net. i create one database in sql server. now, i want to make setup for my app. so, i want copy database and put on c:/..../wwwroot/app folder. how can i do this? one more things, i create database using enterprise manager. i want to see my database file. where it is stored? somebody help me. it's urgent. thanks in advance.
View Replies !
Copying The Database
Hi, I am changing my hosting from one company to another company. How can I copy my full database along with views and stored procedures. I have only access to query analyzer and enterprise manager from where I am not able to backup the database on my local computer. As it is very urgent please suggest me a way to do this. Thanks in advance, Uday
View Replies !
Copying A Database
Hi all, I want to copy a database from my SQL Server system and install it on another. Can anyone suggest me how I can copy the same along with log info, login info, permissions, etc. and install it on another system. Thanks! Vik!
View Replies !
Copying Database
Hey guys I am trying to copy a database from my PC to another PC within the smae network. I am logged into the other PC. I have tried DTS, but was only able to copy the tables in the database. i want to copy the ENTIRE DataBASE. I have also tried using sp_attach, but kept getting a message that the location of the file is wrong. I have checked the location and the file is in that location. can someone please explain to me in "lay man's terms", how to copy the ENTIRE dbase? thank you
View Replies !
Copying A Database
Hello,I'm trying to make a copy of a database with this code Code Snippet private void button1_Click(object sender, EventArgs e) { string dbname = @"D:Documents and SettingsBeheerderMijn documentenVisual Studio 2005ProjectsTest12Test12inDebugDatabase1.mdf"; string constr = string.Format("Data Source=(local)\SQLEXPRESS;AttachDBFilename="{0}";Integrated Security=True;Connect Timeout=30;User Instance=True", dbname); using (SqlConnection connection = new SqlConnection(constr)) { connection.Open(); connection.Close(); } File.Copy("Database1.mdf", "Database1Copy.mdf"); } but once the code tries to execute the File.Copy line it comes with an error that the database is currently in use by another program, and also when I'm debugging the program and I try to copy this database with windows explorer it says that the program is currently in use.I've read up some stuff and found that you can stop the SQLServer Service, wich I did but then my program gave an error about not being able to connect to the database, when starting upand also detaching and attaching a database, wich I don't know how to do.So my question is are any of these above methods correct to do, and how do write the code for it? If not is there any other way to copy a database?Thanks in advance,Ruben Pieters
View Replies !
Copying One Database To Another
Hi all, New member here... I've used SQL2000 before (Enterprise Mgr, Query Analyzer), but, now I'm at a company without any DBAs so I'm on my own as far as administration goes which I haven't done before on SQL2000. We have a production database about 100MB in size. As of now there is no development DB. They won't pay for a new server, so I'd like to create a new development database on the same prod server. Then, I want to copy the existing prod database into the new dev DB. I've read up some on DTS, but I wanted to check with the community to see if anyone had other recommendations or if this is the best way to go about it. Also, is this a process that should run at off-hours? Would the copy of production lock the DB up? Thanks a lot...
View Replies !
Copying A Database
I created a DB on the server. i would like to make a copy of it (structure and data) on my local machine to play with. Then i try using the wizard i get this error: Your SQL Server Service is running under the local account. You need to change your SQL Server Service account to have rights to copy over the net work. Where do I go to change my rights to copy over the net work?
View Replies !
Copying The Database....help Please!!
HI I'm quite new to the admin side of SQL server 2005 and I need help copying a SQL database to my colleagues laptop (we are not on a network) Basically I try to copy the .mdf file so that he can attach it onto his server, but I get the following error: "Cannot copy ....mdf as it is being used by another person or program" Although I am not using it. i.e. i re-start my pc and try to copy it and get the same error. I also try to detach the database from SQL and copy it, but still no luck. Please can anybody give me assistance. Many thanks!!!
View Replies !
Copying The Database
Hi all This is my first post here so be easy on me ;-) I've been trying out SQL Express (on my home PC) over the past couple of days and so far I've managed to create a database, create tables etc. I've managed to link Access 2003 to the database so its a bit easier to manage. I've also managed to link an asp.net page to the database etc. Now, I'd like to copy that database up to my dedicated server to do some more testing/experimenting. Can anyone tell me what the best way to copy the database up to my server? Thanks.
View Replies !
Does SSIS Database Copy Overwrite Existing User Permission To That Database?
Hi guys, I've been assigned the task of setting up access to our SQL Server 2005 box. A consultant developing for us has accessing to 2 databases and I've set this up fine. It appears however that one of these databases is re-copied over to the server every night to keep data reasonably current. I'm not interesting in changing this method as I'm not the maintainer (as yet). Basically I would like to know if I've setup access to this database (it works fine), when the database is updated (with an SSIS package) the account seems to get deleted. Do the original permissions from the source database overwrite those of its destination? Cheers
View Replies !
Granting Permission To A Database User To Alter Database Role
I want a database user to be able to alter login, database user and database role from my application. so, i assigned that user to sccurityadmin server role, db_accessadmin and db_securityadmin database roles....By now, the user can add or remove login and database user. However, the user cannot add or remove any database role membership. What am I missing here?? What should I do so that the user can create, and alter database roles in the database??
View Replies !
Copying A Table From One Database To Another?
Hi,Whats the best and easiest way to copy a table from one database to another via ASP.net?I want to copy from SQL Server to Oracle. I have an ODBC connection for Oracle.I was thinking I could use a DataSet, but wasn't sure of the details on doing this. Am I off base on this?What I have so far..1) I can get the data from SQL Server into a DataSet.2) I can establish a connection to Oracle via ODBC and the database has the appropriate table structure.How do I 'use' the SQL Server DataSet to copy the data to Oracle?Thanks,Scott
View Replies !
Copying A Database And All Contents
Can anyone advise me of a convenient way to make a copy of a database in SQL2005. I need to make a complete copy - including all Stored procedures, functions tables and table contents. I want the place the copy ont he sae SQL server but obviously under a different name.
View Replies !
Copying A Database Diagram
Hi, I have a database diagram on my sql server 2005, how can i copy that diagram to my pen drive so that i can build the database using that diagram on another computer...well the bottom line is i want to copy the datbase from one computer to another. thanksVishal
View Replies !
Sql Express Copying A Database?
Hi All,I have a database that I would like to copy for another project. The instance is SQLEXPRESS, I have copied the MDF file, renamed and then copied it back but how to I attach this.I have read a small article about XCOPY but could really understand it, and are there limitation of doing it this way ... i just want an exact copy in the same instance ?Anyone got any links to tuttorials for doing this?Thanks in advancelee
View Replies !
Copying/ Backing Up A Database
Hi. im shortly going to have to submit my project for uni which ive created using sql server. How can i copy everything that ive made so i can submit everything and it can be replicated if necessary. Do i use the backup database task in enterprise manager or do i have to do that and export data or..? ive used tables and stored procedures and a diagram btw. thanks for any advice
View Replies !
Copying One SQL Server Database Into Another
Hi, I'm trying to copy one SQL Server Database into another SQL Server DB within the same server. I learnt a bit about this copying, that it copies only the Database structure, the Tabels, constraints view stored procedures etc etc. Is there a tutorial where I can get clear instructions to do this. I just need the table structure without any data to be copied into this. Have even tried exploring creating and executing DTS packages but cudn't get much help with this. Any help wud be really appreciated. Thanks
View Replies !
Copying Database From One Server To Another
We recently got a new SQL Server 2000. I'm not really a SQL/Network admin but I was tasked to migrate some of our databases in the SQLSVR7 to SQLSVR2K. I tried using DTS EXPORT but getting errors. Is there a better way to do this? Any info would be appreciated.
View Replies !
Copying MSDE Database
I have a MSDE database on a laptop and need to copy it to another machine in order to use Access 2000 to inspect the tables and view the data using an Access project adp file. Could someone please tell me how to do this and whether there are any relevant issues/problems. thanks
View Replies !
Copying A SQL Server Database
Hi I am new to SQL Server. I am required to make a copy of a live database, or bring the database down and then make a copy of it. I need to create a webpage to query the database, but another scheduling program also connects to the database, so that shceduling can be performed Any help
View Replies !
Error On Copying The Database
Hi guys, I can't find what is wrong. I am copying a database via DTS and I get an error mid way "Invalid object dbo.fx_get_role_from_ownerid". But the object does exist and it has benn in production for a while now. Why would I get his error, if the object does exist?
View Replies !
Copying Users From One Database To Another
I have a question regarding copying users from one server to another that are running different versions of Microsoft Sequel Server. I have Server A running 6.5 version and Server B running 7.0 version. How can I copy just the list of users from Database1 on Server A to Database2 on Server B. Can anyone help me. Thanks. Lakshmi.
View Replies !
Copying A Database From One Server To Another
Hi, Does anyone know how to copy a database from one server to another? I want to backup a production database and restore it to a different server (a test server) as a test. I used a sample from the MCDBA study book and all I got was two copies of Northwind on the same server. Any idea what I did wrong? Seems like you should be able to backup on one server and restore on another. Here's the code I used: BACKUP DATABASE Northwind TO DISK = 'Sd-appsmssql7DataNwind.bak' RESTORE FILELISTONLY FROM DISK = 'Sd-appsmssql7DataNwind.bak' RESTORE DATABASE Northwind2 FROM DISK = 'Sd-appsmssql7DataNwind.bak' WITH MOVE 'Northwind' TO 'Sd-sqlmssql7DataNorthwind.mdf', MOVE 'NorthwindLog1' TO 'Sd-sqlmssql7DataNorthwindLog1.ldf' GO Thanks for your help.
View Replies !
Copying And Renaming Database
Hello, I have created a complete database with its tables, views, procedures, everything in SQL Server for a system. Now I am creating another system and I have realised that I can use almost the same fields I have and only do small changes, therefore I want to copy the existing one and rename it I have been trying to copy it but I don't know how to copy exactly everything. Could you help me please? Any help would be really appreciated! Thank you Gloria
View Replies !
Copying Database Offline
I'm going overseas this summer with my laptop and I have to continue working on a project. Unfortunately that laptop only has Windows Vista Home Premium. I can't afford to get the upgrade but am in the position of having to copy a database from the server to my laptop so I can continue working on it. What I need to do is find a way to copy the database over but in the case where the two computers aren't connected over a network. Is this possible? I tried to simply copy the .mdb, but there were permissions errors (of course). Is there a way to do this? It's pretty important that I get it on the laptop one way or another. Thank you!
View Replies !
Copying Entire Database
Hi, Is it possible to copy a database from the existing database. I mean to say creating a duplicating database. Note: I can do it by using "Generate SQL Server Script Wizard" or "Database Publishing Wizard" which saves a some filename.sql and later executing this will create another database by assigning some xyzdatabase name. My main motive is copying a database in a single line script without using this lengthy sciprt. Example FirstDatabase (contains 10 tables 10 stored procedures) SecondDatabase (also contains 10 tables 10 stored procedures) If possible, can you write a script that attach a database from the C:MyTemporaryDatabase Because this i need through programming Visual Basic using SQL Server. I want to create some temporary database. Waiting for your reply. Thanks. Best Regards Kashif Chotu
View Replies !
Copying Structure To Another Database
Hi all, Is there a way to copy a structure of one database (tables and fields) to another database WITHOUT copying the data?? Someway to do that with replication?? any help is very appreciated! Thank you,
View Replies !
Copying Table From One Database To Another
How can you copy a table from one database to another database? I used to be able to do it with SQL Server 2000 but I can't figure out how to do it with SQL Server Management Studio Express for SQL Server 2005. Do I need other software? Dave
View Replies !
Copying Data From One DataBase To Another?
Yet another very much newbie question I suspect€¦. I want to know how I copy data from one field in one DataBase to another field in another DataBase?? Basically I have an ASP driven forum, and want to upgrade to a new ASP.NET forum€¦ Problem is obviously the DataBase structure is different and I don€™t want to loose all the data from my current forum€¦ So I would have to pick which table field needs to be copied to where in the new DataBase.. Is this easy to do?? I will be using SQLExpress 2005?? Thanks
View Replies !
Copying A Database To Another Server
Im running SQL Server 2005 Express with SQL Server Management studio installed on my laptop. After updating my database, how do I copy the entire contents to the main server in the office, which is running windows server 2003 and SQL Server 2000 Developer Edition. And is it possible to do it all from my laptop?
View Replies !
Copying A Database Between Servers
I am attempting to move a User Database from the Production Server to a Training Server. What is the best/most simplistic way for me to accomplish this task and place this copying action on a schedule of say "every saturday morning @ 7am" I tried the "Copy Database Wizard" within Enterprise Manager and it successfully copies the database, however when I try to schedule it to happen at a different time...it does not copy the database. It seems to only work when I tell it to perform this action now. Please help. Thanks, JC
View Replies !
Copying And Renaming Database
Hello, I have created a complete database with its tables, views, procedures, everything in SQL Server for a system. Now I am creating another system and I have realised that I can use almost the same fields I have and only do small changes, therefore I want to copy the existing one and rename it I have been trying to copy it but I don't know how to copy exactly everything. Could you help me please? Any help would be really appreciated! Thank you Gloria
View Replies !
Copying The Database Schema
Hi Friends We have a database that contains tables having 4 million records Can you tell me a way to copy the schema of the Database to a new database. Generate SQL Script...Is it the only option or can i use the DTS package Which is the most reliable one? Regards Vivek Vic http://vicdba.blogspot.com
View Replies !
Copying A Database From Web Host
Hi. I have a client with a database hosted on SQL Server 2005. I don't have a paid version of SQL Server at home so am just using SQL Server Express. I'm getting INCREDIBLY FRUSTRATED that I can't use the "copy database" wizard from SQL Server Express. Am I just out of luck on this? I can't use the "copy database" feature since I'm using Express. I can't get the actual files since the database is on a shared server. I know I'm not the only person with this issue; I see people asking the question but I don't see any answers. Is there any way I can copy this database? Thank you very very much, - Andrew Clarke.
View Replies !
|
TRACKER
