Re: object can't be accessed unless owner is specified - SQL 2000

THE.Ronald (THERonald@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
"Erland Sommarskog" wrote:
The you did the restore, and now the SELECT above would fail with error
208, Object not found. However, "SELECT ... FROM Sune.tbl" would fail.

The only reasonable explanation is that at this point Sune was the
database owner, and thus the login Sune mapped to dbo and had dbo
as its default schema.


Thx for the reply. Both did NOT fail, the qualfied query with the owner
worked.

Sorry, I lost a "not" there. The first paragraph was intended to end
"...would not fail".

Sune (continuing your example) was the database owner and owned the
objects as well.

If login Sune was the database owner, he mapped to the user dbo, and
thus his default schema was dbo. To access objects in Sune schema,
he would need to prefix them with the schema.

Does SQL 2000 actually have schemas ? It mimics them, but I didn't think
there was a place to define that.

SQL 2000 certainly has schemas! But there is a one-to-one mapping to
owner, so we mainly think of them as owners, rather than schemas. Or
at least we used to think until we saw the light with SQL 2005.




--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.

Relevant Pages

  • Re: Viewing object owner in SQL 2005 - ownership chaining
    ... preferred it if SQL 2005 supported EXECUTE AS for views). ... I'm beginning to understand that OWNER lives somewhere between the ... the owner of the schema the object was created it. ... In many databases, dbo owns everything. ...
    (microsoft.public.sqlserver.security)
  • Re: Access 2007, SQL 2000
    ... Since users are not dbo, and SQL 2000 used the user name as the schema identifier, you have to make sure that every reference to a SQL Server object is fully qualified with the dbo. ... Normally a user should not be able to create any new objects in the db, but if they do, those new objects will be in the userName schema, not the dbo schema, assuming the user is not a db owner. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Stored Procedure Security Problem
    ... CREATE SCHEMA schema_1 AUTHORIZATION schema_1 ... This isn't valid SQL 2000 syntax, even though an error isn't raised. ... The specified owner must be an existing user and, as far as I know, is only used to validate that the specified user exists: ...
    (microsoft.public.sqlserver.security)
  • Re: freelists and dba_free_space
    ... Do you have to put place holders for the parameters that you don't want ... resides in SYS's schema. ... I tried granting EXECUTE ANY PROCEDURE to the schema owner and ... SQL> desc sys.dbms_stats ...
    (comp.databases.oracle.server)
  • AS/400 DB2 adapter to SQL adapter =?UTF-8?B?ZXJyb3LigI8=?=
    ... WWTSREG is the name of the table/file from the DB2 file (also the table node of schema file) and I don't know why the orchestration simply neglects the schema mapping that I created and still uses the source schema for the SQL send port. ... Re: Using stored procedure properly ...
    (microsoft.public.biztalk.general)