SQL Connection String

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

Hi,

I want to access a database which has a different owner. The database
name is the following: Domain\owner.DatabaseName. When I connect as
the Database owner i can just specify 'DatabaseName' as the name.
However when I try as a different user which has r/w access, I always
get the message 'Invalid object name'. I tried all combinations with
and without Domain\owner.

Thanks,
Michael
 
You haven't actually told us which RDBMS this is, so:http://www.connectionstrings.com/
Click to expand...

Thank you. It is MS SQL. The database server is configured to accept
both Windows and SQL Server authentication.
When I specify a username and password (the owner of the database) in
the Connectionstring but different to the Windows user which connects
to it, I get the 'Invalid object name error'
 
Show the connection string you are using.
The database
name is the following: Domain\owner.DatabaseName.
Click to expand...

It should be just DatabaseName.

My understanding is that you are trying to connect to the database using
some other user :
- this is not done by appending this info before the database name.
- you have specific User ID and Password entry in the connection string (try
the site mentionned earlier for the details)
- If a domain account, you have a Integrated Secuirty=SSPI in the connection
string and impersontation is done at the ASP.NET level...
 
Hi,

I want to access a database which has a different owner. The database
name is the following: Domain\owner.DatabaseName. When I connect as
the Database owner i can just specify 'DatabaseName' as the name.
However when I try as a different user which has r/w access, I always
get the message 'Invalid object name'. I tried all combinations with
and without Domain\owner.
Click to expand...

Different owner or different schema?

If this is SQL Server 2000, then I can guarantee you mean owner. If SQL
Server 2005 or greater, you probably mean schema. For example, when I
create a database I can do the following:

CREATE SCHEMA Joe
CREATE SCHEMA Fred

I can then do this:

CREATE TABLE Joe.Person
(
PersonId int
, PersonName varchar(50)
)

AND

CREATE TABLE Fred.Person
(
PersonId int
, PersonName varchar(50)
)

Now, some databases may use the schema like the owner in 2000, but it is
incorrect.

Here's where the thinking gets tricky, so I will try to take you through
this. Users have a default schema in SQL Server. The "owner" of a schema
has that schema as his default (by default, I am not sure it HAS to be this
way -- but, that is why you can connect as that user without the schema in
front of the table name). It need not be like this, but it is a leftover
from previous versions of SQL Server, where there was an owner, not a
schema.

You can be set up to default to a certain schema, but it does not solve
your issue completely, as maintaining user schemas is time consuming.

The better option is to ALWAYS use schema names, even when the user is
defaulted to the schema. Okay, if the only schema you ever use is the
standard dbo, perhaps you can break this rule.

The short story is this: If you explicitly name the objects in
schema.object format, you can always connect. When you rely on defaults,
you have to make sure the user is defaulted correctly (this is an implicit
connection to an object rather than explicit). Explicit costs a bit more up
front (in typing), but saves a lot afterwards.

Peace and Grace,

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************
 
Back
Top