Have Transferred Database && Users From Sql 2000 To 2005 But How Do I Transfer Their Permissions ?
Aug 14, 2007
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
View 2 Replies
ADVERTISEMENT
Mar 19, 2007
Hi everybody!
I dare say it may seems rather very easy for me, but i'm quite glitching. I'm to transfer all users from SQL2000 to SQL2005.
Has anybody done this?
Thanks in advance!
View 1 Replies
View Related
Jul 20, 2005
I am in need of a utility (stored procedure, third party app, etc)that will help streamline the process of managing and auditing objectpermissions, users and roles on multiple sql server 2000 instanceswith multiple databases on each instance. That's a quick overview ofwhat I need, do I need to be more specific?Thanks.
View 2 Replies
View Related
Mar 8, 2007
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
View 2 Replies
View Related
Sep 6, 2006
I am working on a project that is creating a new application, my area of the project being the migration of data from the old application database, transforming it, and populating the new database.
The transformations to the old data are done in a staging database.
At the end of the process, the staging database ends up with a lot of new applications tables, populated with the migrated legacy data.
We need to move these tables from the staging database to (initially) our test databases, but ultimately what will be the live database.
We have tried using the "Transfer SQL Server Objects Task" in SSIS, but have ran into a problem that a lot of the database tables have default values for columns.
These default values are not brought over.
Example. Tables contain a "GUID" field, which has a default of value of newid()
Right clicking and the table generating the CREATE script generates
[GUID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_tbCRM_Client_GUID] DEFAULT (newid()),
However, the Transfer objects task does not create this default of newid()
Examining the SQL generated by the Import / Export Wizard when investigating this shows that the wizard generates this column as
[GUID] uniqueidentifier NOT NULL
and the column default value is lost.
Is there something i should be setting somewhere to force SSIS to bring these column definitions over correctly?
View 10 Replies
View Related
Jul 6, 2007
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
View 5 Replies
View Related
Mar 25, 2008
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.
View 4 Replies
View Related
Feb 2, 2007
Our company has 2 Database Roles (DBE and DBA). The DBE creates
database schema, performs SQL Server Administration, and manages server
security. The DBA writes data access, ETL, and manages database
security. In 2005, we're struggling with how to allow the DBA to see
all of the logins on the server in order to add them as users of their
database. What permissions does the DBA need to select from any of the
logins on the server to add them to their database?
Michelle
View 1 Replies
View Related
Dec 14, 2007
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
View 4 Replies
View Related
Sep 27, 2006
For SQL Server 2000 we have a user login mapped to msdb with database role membership of db_datareader and public checked. This seems to allow the developers to view the Management Activity monitor. For SQL Server 2005 the same mapping is in place but the developers cannot view the Management Activity monitor. Developers are NOT granted the sysadmin role, and should not have that role.
What permissions need to be set for SQL Server 2005 to allow users to view the Management Activity monitor? They should not be allowed to take actions on the activities.
View 13 Replies
View Related
Apr 25, 2008
I've had issues where backup up and restoring data from sqlserver2005 does not reattach the data to the correct users. Any tips on how to best accomplish full database moves where data is owned by different security users?
thanks,
View 2 Replies
View Related
Mar 19, 1999
Hello,
I am trying to figure out a method of transferring all objects from a SQL 6.5 database to a new SQL 7.0 server.
Right now, the only method I can see is to upgrade my SQL 6.5 server to 7.0. I tried to utilize 7.0's DTS but I
could not figure out how to transfer ALL objects into 7.0.
Any ideas?
Chad
View 1 Replies
View Related
Apr 27, 2007
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?
View 2 Replies
View Related
Jan 4, 2008
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
View 9 Replies
View Related
May 28, 2008
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.....
View 4 Replies
View Related
May 13, 2008
Hi
How to transfer logins from 2000 to 2005?
Raj.
View 1 Replies
View Related
Feb 16, 2007
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
View 3 Replies
View Related
Feb 1, 2006
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
View 1 Replies
View Related
Oct 23, 2006
What is the best way to move users from a 2000 sql server to a 2005 server? I have a bunch of issues with the passwords etc...
thanks for your time.
View 5 Replies
View Related
Jan 3, 2006
Hi guys,
I am new to SQL Server 2000 and like to enquire the following.
Scenario:
this current website is hosted by A but now taken over by B.
B was given SQL server username and password and was instructed to download the database directly from A based on the username n pw given.
So my qns is:
Is it possible to initiate data transfer from one server situated at locationA to another server at locationB that doesnt belong to the same domain? If can, by which ways? Using the DTS method? Or there are some settings to be configure at ODBC in order to initiate the data transfer?
Any experts out there? ;)
View 8 Replies
View Related
Jan 4, 2008
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
View 7 Replies
View Related
Aug 20, 2007
Hello,
I think now MS SQL 2005 support packaging database object into schemas which may be granted access by any of the db users, while SQL 2000 only we could use the database creator user instead of schema. my case is i am working in a system with more than one module, e.g. HR + TaskManagemt.
Both HR and TaskManagement objects are in the same database, and there are some common objects.
I want to isolate the HR objects from the TaskManagemt objects, so i can package any module separated from the other, suppose i want to buy only the HR module, so that i want only to exctract the HR Object + the common objects but not the TaskManagement objects.
So how can i accomplish such operations using SQL 2005 schemas AND using SQL 2000 users.
P.S. i have two servers one with 2000 and the other with 2005, so i want to find solutions for both 2000 and 2005
Thanks in advance
View 3 Replies
View Related
Nov 27, 2006
Hi
I'm going to put my database in setup file that I've been ready before.
My Software could be use from database after installing in every system , but other users won't be able to access to data or structure of database , even with adding of database to SQLServe Enterprise Manager .
How Should I can do about this ?
Tanks very much
View 3 Replies
View Related
Oct 6, 2006
Hy ,
How can i transfer a database from Access to Sql Server 2000?
Exactly : i want to transfer a .mdb into .mdf, and i want to keep the structure of .mdb, a structure which have tables, queries, macros, modules... When i start the transfer with Data Transformation Services ( DTS- Import Data ) , i can see that DTS shows me only tables and queries. At the end of transfer, in my .mdf database, on server, i have only tables, without queries, macros, modules.
My question is how can i transfer the queries and macros ( exactly how are in .mdb ) to .mdf database?
Thanks in advance for any advice..
Emanuel.
View 6 Replies
View Related
Dec 2, 2006
Hi,
I would like to know if there will every be a clean way to transfer a database from a remote server to a local server (and back again). I've tried several different approaches, but they all currently have bugs/problems.
I've looked into database publishing wizard - but this creates a huge script that takes 10x longer to run than the old DTS transfer.
I've tried copying data, and objects separate, but this requires multiple steps, using DTS this was all done in one step.
I've tried using SSIS Transfer SQL Objects, but the defaults are not created, using DTS, the ENTIRE database was transfered, which is what I want.
I've tried using SSIS Transfer Database, but even when I select "Online" mode, I get an error asking me to select at least one source file, but there is nothing to select (no files appear in the pop up window).
I've tried copy database or backup database, but I do not have suffient permission on the remote server for these.
So many different ways to do it, but all fail!
PLEASE - make a simple way to transfer a database from a remote host and back again. I want to be able to select a database source and destination and have the database copied. PLEASE - at least restore the old way until other options are working first.
View 15 Replies
View Related
Feb 3, 2006
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
View 13 Replies
View Related
Sep 17, 2007
Hello our organization has never had any problems getting users to connect to our SQL servers but we have grown a lot over the past year or two. Does SQL Server actually block connections if they go over the license limit? I don't have access to the servers to see the model used but I have been asked to look into this. I couldn't find any information on this so I decided to ask you guys. Thanks in advance.
View 4 Replies
View Related
Oct 12, 2006
Does anyone have any great suggestions on how I can import an online XML file into an SQL 2005 table?
So far I tried to accomplish it with SSIS Data Flow Task where my source is XML Source (Data access mode: XML file location; XML location: URL, Use inline schema = True). This set up properly identified the columns to be imported.
I used Copy Column data flow transformation task to load data to OLE DB destination table that has same structure.
When I run the task it does execute with no errors, however the table remains empty. It looks like I am failing to read the actual data.
Do you have any suggestions? I am willing to go around this approach with stored procs/com/you name it €“ just make it work!
Thanks a lot for your help!
LoveDanger
View 6 Replies
View Related
May 9, 2002
Hi there!
I'm developing an application and i need to do the next thing:
I need to make a process, but this process just need to be done, when nobody else is modyfing datas in the database.
Is there anyway i could check this? I have read about the sp_who stored procedure but this one return all the activity in all the databases, and i just need to check in one specifically. And there's another issue, we all access with the user dbo to the database...
Any help will be appreciated..
View 2 Replies
View Related
Jan 19, 2000
I restored a copy of a database onto a new server. I created the proper logins on the new server prior to the restore. The users and permissions should all be identical. They appear to be in the system tables, but none of the users or permissions appear in the appropriate screens on Enterprise Manager.
Also, even though the permissions appear to be intact in the syspermissions table, when I connect as a certain user and try to query a table the user should have permissions on, I'm denied access.
Does anyone have any suggestions?
Thanks in advance!
Lisa Rae
View 4 Replies
View Related
Sep 7, 2007
Is there an SP that can tell the database, the users with access to the database, and their permissions? Meaning the result would be something like...
<database> <username> db_datawriter
<database> <username> db_datareader
<database> <usernameII> db_owner
Thanks in advance
-Kyle
View 3 Replies
View Related
