db1.dbo.table to db2.other.table

Hi,
I need help/guidance setting up replication across dbs.
db1 has the same tables as db2. tables have the same fields.
db1 has "dbo.table" schema, db2 has "other.table" schema.
I setup simple replication and db1.dbo.tables are being added to db2 as
db2.dbo.tables instead of just refreshing data on db2.other.tables...
Any help would be greatly appreciated.
http://www.dbazine.com/sql/sql-articles/cotter1
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<Doug M> wrote in message
news:1058678B-8239-423C-B68D-C17EC9E882A5@.microsoft.com...
> Hi,
> I need help/guidance setting up replication across dbs.
> db1 has the same tables as db2. tables have the same fields.
> db1 has "dbo.table" schema, db2 has "other.table" schema.
> I setup simple replication and db1.dbo.tables are being added to db2 as
> db2.dbo.tables instead of just refreshing data on db2.other.tables...
> Any help would be greatly appreciated.
>

db_owner role and table owner issues

Hi,

I have given a user db_owner role in a database. When he creates a table using Enterprise manager the table owner is dbo. When he creates a table using Query Analyzer the table owner is the user. eg

Enterprise Manager = dbo.Table1

Query Analyer = username.Table1

This causes a problem when the user is writing web applications. Is this an error in the way i have set up permissions ? How can i make them behave the same way?

Thanks for your help.First, don't create tables in EM

Second, it's how they are connecting...

Third, depending on how you want the application to work, qualify the owner...

Fourth, get control...

have them supply you with the DDL, and you create the tables for them...

My guess is that EM is connected with sa, and QA is connecting with their id...

just a guess...|||Thanks for your quick response. I should have included the following information.

I work in a University computing department where students must learn to create tables etc. I could not create their tables for them as it is part of their assessment (and there are 1700 students!)

Students can only log on to the server using windows authentication so will log on using the same windows account to both EM and QA.

Students are taught to use both EM and QA which is why they are finding problems.

Thanks again for all help.|||Gotta test it...which way do you want the tables qualified for their apps...

dbo?

I'll look into it...|||yes dbo thank you.

db_owner problem

I recently registered my hosted database server on my pc. When I ran aspnet_regsql.exe the table created have dbo as schema, but when I create a news table throught stored procedure it has my username as schema.
When I buildt my site on my pc all tables have dbo as schema.
How do I make all tables on my hosted server db have same schema?

Thanks

The very Newbie

The schema of new objects is determined by the default schema of current database user. So just make sure all database users have the same default schema (for example). You can easily do this in Management Studio.|||

Thanks for your reply Iori_Jay.

Where and how in Management Studio do I make sure that default schema is MyUsername(current database user) instead of getting schema "dbo" for some tables and schema "MyUsername" for other tables?
When I ran aspnet_regsql.exe all tables and procedures have schema dbo allthough I use my username to create them.

Thanks again

The Very Newbie
We learn from masters

|||You can check the defaut schema of a database user by checkin the Properties of the user under SQL instance->Databases->your database->Security->Users.|||

Thanks again Iori_Jay,

In the security folder of my database there are 2 users: dbo and myself. I need to transfer schema, but don´t know how to make it work. My hosted server uses sql server 2000.

Thanks

|||

In SQL 2000 SHCEMA is not a seperated object, it equals to the owner of the object. So you need to change to owner of the object using such command:

EXEC sp_changeobjectowner 'myself.tbl1', 'dbo'

|||

Thanks a lotIori_Jay.

It works fine.

sql

DB Role and DBO

Hello, I've created a database role for the application user. They are usin
g
the dbo schema, so when I try to add dbo to the role, it won't let me. Why
is this? And does this mean we would need to create some other schema
besides dbo?
Thanks,
MitchHi
DBO is just "special" user. My guess you are using SQL Server 2005 ,right?
In SQL Server 2005 user owner and schema are separeted. You will have to
read BOL on the subject. SCHEMA is juct a container for many objects for
security purposes
"Mitch" <Mitch@.discussions.microsoft.com> wrote in message
news:D29C96D0-AEFA-4263-BA19-D5DE631F2CA9@.microsoft.com...
> Hello, I've created a database role for the application user. They are
> using
> the dbo schema, so when I try to add dbo to the role, it won't let me.
> Why
> is this? And does this mean we would need to create some other schema
> besides dbo?
> Thanks,
> Mitch

db owner

We are using SQL Express and I have a problem with dbo.
On my machine if I logon to SQL Express using windows authentication
and create a new database I automatically have db_owner role membership
on the new database.
On a colleagues machine, if he logs onto his SQL Express using windows
authentication and creates a new database he does NOT have db_owner role
membership on the new database.
How come?
I have checked pretty much everythng - windows built in admins, SQL express
sysadmin roles, service pack versions but I can not find any difference in
setup.
What should I do to his machine/SQL Express setup so he automatically has
db_owner role membership for every newly created database?
Thanks
CharlieCharlie
What if you create login as the following
CREATE LOGIN Charlie WITH password='pp'
CREATE USER Charlie FOR LOGIN Charlie
WITH DEFAULT_SCHEMA=dbo
EXEC sp_addrolemembe 'db_owner','Charlie'
"Charlie" <Charlie@.discussions.microsoft.com> wrote in message
news:0ECAEB63-1450-40C9-B51D-01F5F4C9B92E@.microsoft.com...
> We are using SQL Express and I have a problem with dbo.
> On my machine if I logon to SQL Express using windows authentication
> and create a new database I automatically have db_owner role membership
> on the new database.
> On a colleagues machine, if he logs onto his SQL Express using windows
> authentication and creates a new database he does NOT have db_owner role
> membership on the new database.
> How come?
> I have checked pretty much everythng - windows built in admins, SQL
> express
> sysadmin roles, service pack versions but I can not find any difference in
> setup.
> What should I do to his machine/SQL Express setup so he automatically has
> db_owner role membership for every newly created database?
>
> Thanks
> Charlie
>

db out of space

When I try to backup a table with a query such as:
SELECT * INTO dbo.SiteNotes_COPY FROM dbo.SiteNotes
I am getting an error indicating my database is out of space. Note that the
error message refers to my active database, *not* the tempdb as I have seen
in various other posts both on this board (e.g. "tempdb 2005 out of space" by
Dan D.) and on others.
Here is my error message:
"Msg 1101, Level 17, State 2, Line 1
Could not allocate a new page for database 'ProdDB' because of insufficient
disk space in filegroup 'PRIMARY'. Create the necessary space by dropping
objects in the filegroup, adding additional files to the filegroup, or
setting autogrowth on for existing files in the filegroup."
From database properties:
--Autogrowth was set to "By 1MB, unrestricted" so I increased that to 5MB
but I still have the same problem. The initial size is listed as 4096MB
From table properties:
--The table in question occupies 22MB (81,000 rows).
From Windows Explorer:
--The .mdf file size is shown as 4.00GB so it is clearly a growth boundary
issue.
--My disk has plenty of space (44GB)
Other operations with some other large tables have shown the same error
message.
Is there a cure for this malady?
(System: I am running SqlServer2005 Express on WinXP Pro SP2.)
SQL Server Express has a hard limit of 4GB per database. We could
have hoped for a more informative messages, but that is the limit you
have encountered.
Roy Harvey
Beacon Falls, CT
On Tue, 12 Jun 2007 16:11:00 -0700, michael sorens
<m_j_sorens@.newsgroup.nospam> wrote:

>When I try to backup a table with a query such as:
> SELECT * INTO dbo.SiteNotes_COPY FROM dbo.SiteNotes
>I am getting an error indicating my database is out of space. Note that the
>error message refers to my active database, *not* the tempdb as I have seen
>in various other posts both on this board (e.g. "tempdb 2005 out of space" by
>Dan D.) and on others.
>Here is my error message:
>"Msg 1101, Level 17, State 2, Line 1
>Could not allocate a new page for database 'ProdDB' because of insufficient
>disk space in filegroup 'PRIMARY'. Create the necessary space by dropping
>objects in the filegroup, adding additional files to the filegroup, or
>setting autogrowth on for existing files in the filegroup."
>From database properties:
>--Autogrowth was set to "By 1MB, unrestricted" so I increased that to 5MB
>but I still have the same problem. The initial size is listed as 4096MB
>From table properties:
>--The table in question occupies 22MB (81,000 rows).
>From Windows Explorer:
>--The .mdf file size is shown as 4.00GB so it is clearly a growth boundary
>issue.
>--My disk has plenty of space (44GB)
>Other operations with some other large tables have shown the same error
>message.
>Is there a cure for this malady?
>(System: I am running SqlServer2005 Express on WinXP Pro SP2.)
|||Thank you for solving my mystery!

db out of space

When I try to backup a table with a query such as:
SELECT * INTO dbo.SiteNotes_COPY FROM dbo.SiteNotes
I am getting an error indicating my database is out of space. Note that the
error message refers to my active database, *not* the tempdb as I have seen
in various other posts both on this board (e.g. "tempdb 2005 out of space" b
y
Dan D.) and on others.
Here is my error message:
"Msg 1101, Level 17, State 2, Line 1
Could not allocate a new page for database 'ProdDB' because of insufficient
disk space in filegroup 'PRIMARY'. Create the necessary space by dropping
objects in the filegroup, adding additional files to the filegroup, or
setting autogrowth on for existing files in the filegroup."
From database properties:
--Autogrowth was set to "By 1MB, unrestricted" so I increased that to 5MB
but I still have the same problem. The initial size is listed as 4096MB
From table properties:
--The table in question occupies 22MB (81,000 rows).
From Windows Explorer:
--The .mdf file size is shown as 4.00GB so it is clearly a growth boundary
issue.
--My disk has plenty of space (44GB)
Other operations with some other large tables have shown the same error
message.
Is there a cure for this malady?
(System: I am running SqlServer2005 Express on WinXP Pro SP2.)SQL Server Express has a hard limit of 4GB per database. We could
have hoped for a more informative messages, but that is the limit you
have encountered.
Roy Harvey
Beacon Falls, CT
On Tue, 12 Jun 2007 16:11:00 -0700, michael sorens
<m_j_sorens@.newsgroup.nospam> wrote:

>When I try to backup a table with a query such as:
> SELECT * INTO dbo.SiteNotes_COPY FROM dbo.SiteNotes
>I am getting an error indicating my database is out of space. Note that the
>error message refers to my active database, *not* the tempdb as I have seen
>in various other posts both on this board (e.g. "tempdb 2005 out of space"
by
>Dan D.) and on others.
>Here is my error message:
>"Msg 1101, Level 17, State 2, Line 1
>Could not allocate a new page for database 'ProdDB' because of insufficient
>disk space in filegroup 'PRIMARY'. Create the necessary space by dropping
>objects in the filegroup, adding additional files to the filegroup, or
>setting autogrowth on for existing files in the filegroup."
>From database properties:
>--Autogrowth was set to "By 1MB, unrestricted" so I increased that to 5MB
>but I still have the same problem. The initial size is listed as 4096MB
>From table properties:
>--The table in question occupies 22MB (81,000 rows).
>From Windows Explorer:
>--The .mdf file size is shown as 4.00GB so it is clearly a growth boundary
>issue.
>--My disk has plenty of space (44GB)
>Other operations with some other large tables have shown the same error
>message.
>Is there a cure for this malady?
>(System: I am running SqlServer2005 Express on WinXP Pro SP2.)|||Thank you for solving my mystery!

db out of space

When I try to backup a table with a query such as:
SELECT * INTO dbo.SiteNotes_COPY FROM dbo.SiteNotes
I am getting an error indicating my database is out of space. Note that the
error message refers to my active database, *not* the tempdb as I have seen
in various other posts both on this board (e.g. "tempdb 2005 out of space" by
Dan D.) and on others.
Here is my error message:
"Msg 1101, Level 17, State 2, Line 1
Could not allocate a new page for database 'ProdDB' because of insufficient
disk space in filegroup 'PRIMARY'. Create the necessary space by dropping
objects in the filegroup, adding additional files to the filegroup, or
setting autogrowth on for existing files in the filegroup."
From database properties:
--Autogrowth was set to "By 1MB, unrestricted" so I increased that to 5MB
but I still have the same problem. The initial size is listed as 4096MB
From table properties:
--The table in question occupies 22MB (81,000 rows).
From Windows Explorer:
--The .mdf file size is shown as 4.00GB so it is clearly a growth boundary
issue.
--My disk has plenty of space (44GB)
Other operations with some other large tables have shown the same error
message.
Is there a cure for this malady?
(System: I am running SqlServer2005 Express on WinXP Pro SP2.)SQL Server Express has a hard limit of 4GB per database. We could
have hoped for a more informative messages, but that is the limit you
have encountered.
Roy Harvey
Beacon Falls, CT
On Tue, 12 Jun 2007 16:11:00 -0700, michael sorens
<m_j_sorens@.newsgroup.nospam> wrote:
>When I try to backup a table with a query such as:
> SELECT * INTO dbo.SiteNotes_COPY FROM dbo.SiteNotes
>I am getting an error indicating my database is out of space. Note that the
>error message refers to my active database, *not* the tempdb as I have seen
>in various other posts both on this board (e.g. "tempdb 2005 out of space" by
>Dan D.) and on others.
>Here is my error message:
>"Msg 1101, Level 17, State 2, Line 1
>Could not allocate a new page for database 'ProdDB' because of insufficient
>disk space in filegroup 'PRIMARY'. Create the necessary space by dropping
>objects in the filegroup, adding additional files to the filegroup, or
>setting autogrowth on for existing files in the filegroup."
>From database properties:
>--Autogrowth was set to "By 1MB, unrestricted" so I increased that to 5MB
>but I still have the same problem. The initial size is listed as 4096MB
>From table properties:
>--The table in question occupies 22MB (81,000 rows).
>From Windows Explorer:
>--The .mdf file size is shown as 4.00GB so it is clearly a growth boundary
>issue.
>--My disk has plenty of space (44GB)
>Other operations with some other large tables have shown the same error
>message.
>Is there a cure for this malady?
>(System: I am running SqlServer2005 Express on WinXP Pro SP2.)|||Thank you for solving my mystery!

db name changed

about 1 week back, I created a DB dbo as owner. After this i never touched
this db. We have about 5 DBA with full previlage. Suddlenly when i checked
today owner name is changed to my login. I am 100 % sure i never touched.
Is there any way to find, when name is changed? i checked log file i did n't
get any information.
this is very urgen issue, could any one of you guys help me how to find when
owner ship is changed.?
thanks
kalyan
Hi
That information is not logged. The only way you could do the change is to
use sp_changedbowner.
You can not create a DB with dbo as owner. An owner of a DB can only be
someone who is is master.dbo.syslogins. dbo is a role inside a DB.
When you created the DB, your login become owner for it, and unless you did
it when logged in as sa, or ran sp_changedbowner, it remains in your name.
Regards
Mike
"Kalyan" wrote:

> about 1 week back, I created a DB dbo as owner. After this i never touched
> this db. We have about 5 DBA with full previlage. Suddlenly when i checked
> today owner name is changed to my login. I am 100 % sure i never touched.
> Is there any way to find, when name is changed? i checked log file i did n't
> get any information.
> this is very urgen issue, could any one of you guys help me how to find when
> owner ship is changed.?
> thanks
> kalyan

db name changed

about 1 week back, I created a DB dbo as owner. After this i never touched
this db. We have about 5 DBA with full previlage. Suddlenly when i checked
today owner name is changed to my login. I am 100 % sure i never touched.
Is there any way to find, when name is changed? i checked log file i did n't
get any information.
this is very urgen issue, could any one of you guys help me how to find when
owner ship is changed.?
thanks
kalyanHi
That information is not logged. The only way you could do the change is to
use sp_changedbowner.
You can not create a DB with dbo as owner. An owner of a DB can only be
someone who is is master.dbo.syslogins. dbo is a role inside a DB.
When you created the DB, your login become owner for it, and unless you did
it when logged in as sa, or ran sp_changedbowner, it remains in your name.
Regards
Mike
"Kalyan" wrote:

> about 1 week back, I created a DB dbo as owner. After this i never touche
d
> this db. We have about 5 DBA with full previlage. Suddlenly when i checke
d
> today owner name is changed to my login. I am 100 % sure i never touched.
> Is there any way to find, when name is changed? i checked log file i did n
't
> get any information.
> this is very urgen issue, could any one of you guys help me how to find wh
en
> owner ship is changed.?
> thanks
> kalyan

db name changed

about 1 week back, I created a DB dbo as owner. After this i never touched
this db. We have about 5 DBA with full previlage. Suddlenly when i checked
today owner name is changed to my login. I am 100 % sure i never touched.
Is there any way to find, when name is changed? i checked log file i did n't
get any information.
this is very urgen issue, could any one of you guys help me how to find when
owner ship is changed.?
thanks
kalyanHi
That information is not logged. The only way you could do the change is to
use sp_changedbowner.
You can not create a DB with dbo as owner. An owner of a DB can only be
someone who is is master.dbo.syslogins. dbo is a role inside a DB.
When you created the DB, your login become owner for it, and unless you did
it when logged in as sa, or ran sp_changedbowner, it remains in your name.
Regards
Mike
"Kalyan" wrote:
> about 1 week back, I created a DB dbo as owner. After this i never touched
> this db. We have about 5 DBA with full previlage. Suddlenly when i checked
> today owner name is changed to my login. I am 100 % sure i never touched.
> Is there any way to find, when name is changed? i checked log file i did n't
> get any information.
> this is very urgen issue, could any one of you guys help me how to find when
> owner ship is changed.?
> thanks
> kalyan

DB help needed

hi everyone..
i am seeing this error when connecting to the DB.

Login failed for user 'dbo'. Reason: Not associated with a trusted SQL Server connection.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'dbo'. Reason: Not associated with a trusted SQL Server connection.

can anyone tell me wat is the problem?
thanks for any help..experiencing another error..
can anyone help?
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'h'

thanks|||This means you are referring to a non-existent column named h.

Please provide your code so we can help you to isolate the problem.

Terri|||thanks very much terri.
i have solved the problem..|||encountered this error while doin binding to datagrid
can anyone help me? thanks very much

* Error while binding data
General network error. Check your network documentation.
.Net SqlClient Data Provider