Re: SQL Connection String

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Michael <mail@xxxxxxxxx> wrote in news:bd362da0-72b4-4076-9d4c-
ec596cdd926f@xxxxxxxxxxxxxxxxxxxxxxxxxxxx:

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.

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! |
*******************************************
.

Relevant Pages

  • Re: Need help on how to organize users and objects
    ... Yes Oracle is a bit different than Sql Server, but I think you will get the ... It doesnt become part of any schema until you assign it. ... a new developer started to work. ... need the COMMON database which stores tables with generic data and generic ...
    (microsoft.public.sqlserver.security)
  • xSQL Object 2.5.2.7
    ... synchronizing SQL Server databases. ... Server 2000 database with a SQL Server 2005 database and generate ... history of the schema changes and provide for safe and ... differences, view the scripts of objects, filter results and more; ...
    (comp.software.shareware.announce)
  • Re: SQL Server Express
    ... GRANT CREATE DATABASE on DATABASE::xxx to SAM ... The Windows users have rights because they are administrators. ... "SQL Server and Windows authentication mode".. ... database users, and then define the schema, and this is working. ...
    (microsoft.public.sqlserver.msde)
  • Re: string from column value
    ... Pro SQL Server 2000 Database Design ... What is wrong with the schema? ... >> Pro SQL Server 2000 Database Design ...
    (microsoft.public.sqlserver.programming)
  • Re: 3 Simple Security SQL Statements
    ... In SQL 2005, when you create an object, you specify the schema that the ... Kalen Delaney, SQL Server MVP ... Schema for a database role, that seems like the best setup. ... EXEC sp_addrolemember 'WebUsersRole', 'WebUser' ...
    (microsoft.public.sqlserver.security)