Changing The Schema Ownership On Subscriber
Aug 18, 2006
Hi,
I have a replication in which publisher, distributer and subscriber all runing on sql server 2005. all the tables that needs to be replicated are under 'dbo' schema on publisher.
Subscriber is a datawarehouse so i dont want to put tables coming from a system to go under dbo schema as there might be other application replicating same name tables to warehouse. i have created a schema for my application on warehouse but dont know how to tell replication to create tables under application schema created on subscriber. I am using snapshot replication that can be reinitialized if required.
any help will be appriciated.
Cheers,
Furrukh baig
View 1 Replies
ADVERTISEMENT
Apr 3, 2007
Greetings,I read msdn article and has the following:Transferring ownership of a view to the schema owner The following example transfers ownership the view ProductionView06 to the owner of the schema that contains it. The view is located inside schema Production. ALTER AUTHORIZATION ON OBJECT::Production.ProductionView06 TO SCHEMA OWNER;GOso what i understand that i can create an object within a schema that has a different owner from the owner of the schema!!!is this true ? so if if it is true so what the benefits to create an object within a schema that has a different owner from the owner of the schema.?your help is highly appreciatedbest regards
View 2 Replies
View Related
May 6, 1999
Hi !!
I would greatly appreciate your help. I am a newbie at SQL Server..and am a
trainee DBA for my company. the problem I am facing is that till now the
developers were using any one of three (user created)logins and were creating
and manipulating objects. All the creation and modification of objects and
databases will be done by me. The problem is that the owners of the previously
created objects is one of those logins.
Is there anyway in which I can become the owner of all the objects and deny
them any rights on altering the objects ?? or do I have to drop them all and
recreate them with me as the DBO ?? Thanx
View 1 Replies
View Related
Jan 3, 2007
Hi,
Just want to ask that if it is possible to change a table ownership?
thanks
View 9 Replies
View Related
Jan 9, 2007
Hi all,
I have a problem while executing a stored procedure. I have created a database called "cpd" and created some stored procedures. for all my stored procedure the owner is "CPDUSER". when ever i am executing any stored procedures i have to write the user name first else it is not working.
let's say i have a stored procedure called "cp_checklogin". it takes 2 parameters. to execute this i have to write
exec cpduser.cp_checklogin 'admin@jk.com', 'admin'
but i don't want to write the user name there. and if without username "CPDUSER" i am trying to execute the stored procedure it is throwing me the error that "the stored procedure cp_checklogin is not exist in the database". can anybody suggest me. it's very urgent.
Thanks in advance
Krishna
View 4 Replies
View Related
Feb 20, 2007
Hi all--I used the following type of script to migrate tables, views and stored procedures from 'dbo' to 'hqmsuser':
alter authorization on dbo.PK_T_PQ_MASTER to HQMSUSER
alter schema HQMSUSER transfer dbo.PK_T_PQ_MASTER
go
This was per recommendations from the BOL to use instead of the 'sp_changeobjectowner' procedure. I returned the following error:
Msg 15346, Level 16, State 1, Line 3
Cannot change owner for an object that is owned by a parent object. Change the owner of the parent object instead.
Msg 15347, Level 16, State 1, Line 4
Cannot transfer an object that is owned by a parent object.
How do I find and change the parent object on this from 'dbo' to a non-dbo schema ownership?
View 1 Replies
View Related
May 14, 2008
We have a database that originally had all objects owned by a vendor id. We are in the process of changing the ownership to 'dbo' and wish to remove the vendor id but have found there are a few user defined datatypes that are also owned by this vendor id. How can I change the ownership of them to 'dbo'? They are currently being used in some of the tables. I tried the sp_changeobjectowner but that doesn't work for these.
thanks for any advice!
View 3 Replies
View Related
May 16, 2007
Hi
I need to setup transactional replication for a production server.
The tables (articles) at publisher being replicated can expect schema changes.
Is there any way to reflect schema changes at subscriber using transactional replication.
Early Thanks,
Salman Shehbaz.
View 1 Replies
View Related
Sep 26, 2007
Hi ,
I urgently need a solution for this. I have configured merge replication between a webhoster and a local machine
and it is working perfectly but when I am adding an article (table) which has a CLR assembly associated with it,
the merge replication is failing saying it cannot apply the schema of the CLR assembly at the client. I think Microsoft
supposedly says in it's documentation that merge replication supports CLR assemblies. I have recreated the snapshot
after selecting the table (article) and I have reinitialised the subscription also but it is just not working. It is the simplest
of replication with just one publisher and one subscriber.
Thanks,
Andy
View 2 Replies
View Related
Aug 15, 2006
Hi,
I have the same database(3GB) in 2 physical locations in usa and europe
I need to create a publisher and subscriber between these 2 database without sending any snapshot because i have the same data in the 2 sides, is it possible ?
Thx
Jim
View 13 Replies
View Related
Sep 14, 1999
Hi Nerds
There are 2 production servers (SQL Server 6.5). One is publisher and the other one is subscriber. Data is replicated from publisher to subscriber which is a transaction based replication. Each day at 8:00PM data comes into publisher and the updations,replication,backup,reporting are carried out till 11:00AM next day. The subscriber server is used as a reporting server and the publishing server is used as the data warehouse server.
I want to cleanup a table on the publisher. Since it is replicated on the subscriber, can I do delete operation on the subscriber, make the subscriber server a publisher and the publisher server a subscriber, do replication from publisher(new) to subscriber(new) and again change publisher to subscriber and subscriber to publisher without affecting the production line of the databases. I have to do this between 11:00AM to 8:00PM.
Pranav.
View 1 Replies
View Related
Mar 9, 2006
Hi
Using merge replication - the stored procs are published articles.
When trying to change the article at the subscriber (sql express) I get the following errors:
Msg 21531, Level 16, State 1, Procedure sp_MSmerge_alterschemaonly, Line 45
The DDL statement cannot be performed at the Subscriber or Republisher.
Msg 21530, Level 16, State 1, Procedure sp_MSmerge_ddldispatcher, Line 181
The DDL operation failed inside merge DDL replication manipulation.
Msg 3609, Level 16, State 2, Procedure usp_Anniversary_upd, Line 21
The transaction ended in the trigger. The batch has been aborted.
Does this mean it can only be changed at the publisher ?
Bruce
View 5 Replies
View Related
Sep 25, 2007
Hi all,
here is the context of our problem:
- Publisher :
version SQL Server 2005 SP2
table in SQL Server 2000 (80) compatibility level
publication in SQL Server 2000 compatibility level
- Distributor : SQL Server 2005 SP2
- Subscriber : SQL Server 2000 SP4 + hotfixes (version 8.00.2187)
There is only one article in our publication (a simple table with a GUID and a nvarchar(50) columns), and we have left the default resolver.
1 - The first synchronization of the subscription is successfully completed, and it is the same for the different updates/inserts/deletes or conflicts.
2 - Then we change the Resolver of our article by our own COM-Based Custom Resolver (developped in C# 2.0). This resolver only change the default behaviour: the subscriber always win (this is for a test, in the future we will have a complex business logic). All the synchronizations works fine and do what we want in the conflicts.
3 - We rollback the resolver to the default one... and here is the problem!
The synchonizations stop to work correctly. For all of them we've got the same error:
Code SnippetReplprov.dll , 2007/09/25 14:26:07.591, 4000, 16890, S1, ERROR: ErrNo = 0x80045017, ErrSrc = <null>, ErrType = 8, ErrStr = The schema script '
declare @cmd nvarchar(1000)
set @cmd='exec sys.sp_MSchangearticleresolver @article_resolver=@ar, @resolver_clsid=@rc, @artid=@ai, @resolver_info=@ri'
exec dbo.sp_executesql @cmd, N'@ar> nvarchar(255),@rc nvarchar(40),@ai uniqueidentifie..." mailto:N?@ar">N'@armailto:N'@ar">N'@ar</A< A>> nvarchar(255),@rc nvarchar(40),@ai uniqueidentifie...
It is no more possible to synchronize this subscription... Any remark will be helpfull cause we did not find anything on the net concerning this error.
Thanks a lot!
View 3 Replies
View Related
Mar 3, 2008
Hello everyone. I wanted to learn to assign and change a schema names used in my table. I dont want a dbo. in my tables. I want to give a personal name like the adventureworks database has. (Such as sales.salesdetails, humanresources.employee and all)
> So how i do create a table with a schema name sales
and
> How do i change my for example dbo.employee to sales.employee ??
I tried
Create table sales.employee
{
statements
}
but it doesnt work...
Please reply ASAP
Thanks
into the world of programming now :)
View 6 Replies
View Related
Mar 16, 2007
I allowed the SQL Destination Editor to design my table from Output of
a flat text file. Everything was varchar(50), but that was cool,
because I got to see the data in the new staging tables it created. I
went back and tweaked the data types and sizes for various columns to
be more appropriate through the table designer in SQL Server Management
Studio.
After doing so I get an error trying to edit the package, specifically
parts of the destination in the data flow. I get the error "An error
occurred due to no connection. A connection is required when requesting
metadata... blah blah". I pick the TEST CONNECTION in the Connection
Manager, and it works fine.
I am sure this is probably a basic issue of mechanics of use that I
don't yet know because I am completely new to SSIS. Can someone please
provide a hint, perhaps what I did wrong, and also, if you can see it
how to redeem the error. Thanks!
View 3 Replies
View Related
Jan 24, 2008
I created my database on a remote server. It now has lots of tables and stored procedures
When I created it, the server created a schema named for me, the user, so all my tables and stored procedures are named like johndoe.tablename.
I would like to rename the schema to something less personal more professional.
Can it be as simple as "ALTER SCHEMA johndoe RENAME professional"?
Also, I would like to create a local copy of the databse, so I can develop offline, without the 2 second delay.
SQL Server Management Studio Express lets me generate scripts, which I have been using to make backups.
Can I use the script file to recreate the database on my local machine?
Thanks
View 1 Replies
View Related
Aug 8, 2007
Hello Everyone,
I am working on a dtsx package wherein i am sending the data from OLE DB Source (SQL Server) to OLE DB Destination (Oracle). For development purpose i use DEVLOPMENT environment on oracle but for unit testing i have to use QA or Some other Schema. when i use DEVELOPMENT Schema in ole db destination, tables are accessed under Schema name eg. "DEVELOPMENT"."EMPLOYEE", but when i m chenging schema name to QA table names are not changing as "QA"."EMPLOYEE". Data Flow Task is pushing the data to DEVELOPMENT environment only.
Can Anyone suggest me any remedy for it ?
Or this is one more BUG in SQL Server 2005.
Advice and suggestions are highly appreciated !
Thanks
View 3 Replies
View Related
May 4, 2006
Using a query like
"ALTER SCHEMA TestSchema TRANSFER dbo.table1"
I can easily move "Table1", with schema "dbo", into schema TestSchema. But I can't see how to do it using only the GUI tools provided in Management Studio. Any ideas?
TIA,
Barkingdog
View 1 Replies
View Related
Sep 27, 2007
Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a
database schema compatible with schema version '1'. However, the
current database schema is not compatible with this version. You may
need to either install a compatible schema with aspnet_regsql.exe
(available in the framework installation directory), or upgrade the
provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!
View 4 Replies
View Related
May 27, 2008
I have 35+ tables and 15+ stored procedures with SchemaA, now I want to transfer them to SchemaB.
I know how to do one by one...!
alter schema SchemaB transfer
SchemaA.TableA
but it will take long time...!
Thanks,
View 3 Replies
View Related
Apr 12, 2008
Hello everybody!I'm using ASP.NET 3.5, MSSQL 2005I bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?
View 2 Replies
View Related
May 8, 2007
Hello,
I would like to use SSIS tool to move the data from one database schema to another database schema.
For example:
Source table has
1. UserName (varchar 20) (no null)
2. Email (varchar 50) (can be null)
Destination table has
1. UserID (uniqueidentifier - GUID)
2. UserName (varchar 50) (no null)
3. EmailAddress (nvarchar 50) (can be null)
4. DateTime
Questions:
1. What controls do I use in my Data Flow to make data move between databases with different data types and include new value in UserID as a new GUID and DateTime as a date (GETDATE)?
OLE DB Source, OLE DB Destination, Data Converson and .....
How do I insert Guid and Date at the same time?
2. I have many tables to do data moving. Any sugestions? How do I architect my project? If I create many data flows for each table - it will look complicated.
Please give me some advices here.
Thanks.
View 3 Replies
View Related
Sep 20, 2007
Sometime ago, I had read a posting here about where in the system we can specify default owner of all objects in user database as being a DBO.
The topic came about, because somebody found out that if an object referred to in a query, does not have a fully qualified name, then it takes some time for SQL server to figure out what the owner of the object is; this causes performance problems.
e.g. select * from employees and select * from dbo.employees.
If anybody knows what that setting is...I will really appreciate it
Regards
Paresh Motiwala
Boston, USA
View 4 Replies
View Related
Apr 1, 2008
My system was crashing so I reinstalled everything. including SQL 2005 Express with management studio. I renamed the computer from the previous install. but for some reason the sql 2005 express management studio remembers the old machine name even when I create a new DB inside the management studio with the new computer name.when I try to change the ownership in properties-files it cant find the 'new computer nameuser'i double checked the computer name its correct. The funny thing is i installed express with the 'new computerame' anyone have a clue that may help with this what file needs to be edited?Thanks inadvance for any help.
View 2 Replies
View Related
Jul 5, 2001
Is there a way in SS 7.0 to change ownership of an object from a user owned object to dbo owned object other than dropping the object and re-creating it? E.G. juser.table to dbo.table
Thank you,
GaetonC
View 2 Replies
View Related
Jul 26, 2000
I have tables created by a user called "toms". He has dbo permission etc.
I want to make all these tables to be owned by sa ( dbo). How do I do that?
the owner of the database is sa
thanks,
Rachel
View 1 Replies
View Related
Nov 17, 1999
Is there anyway to change the ownership of a table.. I have developers who create tables and the tables are then shown to be owned by them.. These developers are in the db_owner database role.. The application will not run successfully unless the table is owned by the dbo..
The only way I see that I can handle this is to have them generate a script and have myself (full sa) run it..
Any other suggestions?
Thanks
View 1 Replies
View Related
Nov 2, 1999
I work in a development team of 5 people - when we create DTS packages they are owned by the individuals' user. This means that if a problem is discovered, only the original developer can change their package - which is a problem if they are busy / on holiday etc.
Is there a way to change each others packages without having System Admin priveleges (definitely not an option)
Similarly, how are people handling version control of packages - they is no way to put a package into Sourcesafe / something similar is there ?
View 1 Replies
View Related
Mar 9, 2001
How do you change the ownership of a DTS package?
View 1 Replies
View Related
Jun 28, 2004
I am having a problem with object ownership.
The person who setup the SQL Server setup the "dbo" account with his personal permissions. I need to change "dbo" so that the login name is the system account and not a personal account. Is this possible?
If this is not possible can I change the ownership of a system object?
I was able to change ownershipof user objects using the "sp_changeobjectowner" stored procedure, but it did not seem to work for system objects.
Any way around this?
- Eric
View 2 Replies
View Related
Sep 15, 2004
One of my apps uses a login that is tied down to only use the sprocs and tables it is allowed to access. Its just given 'public' role. However, it needs to be able to add and delete fields from one table in particular. Is there a way to allow this? The login it uses has been given full rights to the table but an alter table command faults. The tbale must be a 'dbo' because other apps use it -- can I be granted DDL rights on a per table basis?
View 6 Replies
View Related
Aug 8, 2007
Ok... Now I'am panicking.... I dont know how to resolve this issue...
Package created by me, imported to sql server by dba, its executed under a different user id (all are NT logins)
Execution Command
Code SnippetDTEXEC /SQL "pkg1" /SERVER SERVER1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
Below is the error.
Code Snippet
Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 9:01:51 AM
Could not load package "pkg1" because of error 0xC0014062.
Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80040E14 (Only the owner of DTS Package 'pkg1' or a member of the sysadmin role may create new versions of it.). The SQL statement that was issued has failed.
Source:
Started: 9:01:51 AM
Finished: 9:01:51 AM
Elapsed: 0.156 seconds
Please some body help me on this.
Thanks
View 8 Replies
View Related
