SQL Server 7 vs 2000 question

[Maze]

This may be a simple question to answer, but I don't have a clue since I'm
not a dba.

I am using sql server 2000, while a client of ours is using sql server 7.
Both using Windows 2000 as an OS, but I don't think that means anything
here.

My question is, why does he have to specify the dbowner in his query
statements, while I don't? Is this a setting somewhere in Enterprise
Manager?

For example, I can simply use the query "select * from tablename" while he
must use something like "select * from sa.tablename".

Any help would be appreciated.

Thanks,
Frank

[Simon Hayes]

Re: SQL Server 7 vs 2000 question

This is unlikely to be related to the version. MSSQL allows multiple objects
to have the same name, but different owners. When you create an object, you
can either specify the owner name explicitly, or leave it blank:

CREATE TABLE MyTable
CREATE TABLE dbo.MyTable
CREATE TABLE MyAccount.MyTab le

If you are a member of sysadmin, db_owner or db_ddladmin, you can create an
object using someone else's user name. If you don't give a username, then
the object will always be owned by your username, or by dbo if you are in
the sysadmin role. When you reference it later in code, you can do the same:

SELECT * FROM MyTable
SELECT * FROM dbo.MyTable
SELECT * FROM MyAccount.MyTab le

If you don't specify a user name, MSSQL looks for an object owned by your
user account. If there is none, it then looks for one owned by dbo. See
"Object Visibility and Qualification Rules" in BOL for examples.

In your scenario, it's not clear who has which permissions, but I guess your
client has created objects owned by sa, and is referencing them explicitly.
You may be accessing your own objects (MyAccount.Tabl e), or dbo-owned
objects (dbo.Table), depending on who created them and what permissions you
have.

Simon

"Maze" <fluyckx@config sc.com> wrote in message
news:bd9pnm$2lq h$1@msunews.cl. msu.edu...[color=blue]
> This may be a simple question to answer, but I don't have a clue since I'm
> not a dba.
>
> I am using sql server 2000, while a client of ours is using sql server 7.
> Both using Windows 2000 as an OS, but I don't think that means anything
> here.
>
> My question is, why does he have to specify the dbowner in his query
> statements, while I don't? Is this a setting somewhere in Enterprise
> Manager?
>
> For example, I can simply use the query "select * from tablename" while he
> must use something like "select * from sa.tablename".
>
> Any help would be appreciated.
>
> Thanks,
> Frank
>
>[/color]

[Maze]

Re: SQL Server 7 vs 2000 question

Thank you Simon, very helpful....

Frank

"Simon Hayes" <sql@hayes.ch > wrote in message
news:3ef89d99_2 @news.bluewin.c h...[color=blue]
> This is unlikely to be related to the version. MSSQL allows multiple[/color]
objects[color=blue]
> to have the same name, but different owners. When you create an object,[/color]
you[color=blue]
> can either specify the owner name explicitly, or leave it blank:
>
> CREATE TABLE MyTable
> CREATE TABLE dbo.MyTable
> CREATE TABLE MyAccount.MyTab le
>
> If you are a member of sysadmin, db_owner or db_ddladmin, you can create[/color]
an[color=blue]
> object using someone else's user name. If you don't give a username, then
> the object will always be owned by your username, or by dbo if you are in
> the sysadmin role. When you reference it later in code, you can do the[/color]
same:[color=blue]
>
> SELECT * FROM MyTable
> SELECT * FROM dbo.MyTable
> SELECT * FROM MyAccount.MyTab le
>
> If you don't specify a user name, MSSQL looks for an object owned by your
> user account. If there is none, it then looks for one owned by dbo. See
> "Object Visibility and Qualification Rules" in BOL for examples.
>
> In your scenario, it's not clear who has which permissions, but I guess[/color]
your[color=blue]
> client has created objects owned by sa, and is referencing them[/color]
explicitly.[color=blue]
> You may be accessing your own objects (MyAccount.Tabl e), or dbo-owned
> objects (dbo.Table), depending on who created them and what permissions[/color]
you[color=blue]
> have.
>
> Simon
>
> "Maze" <fluyckx@config sc.com> wrote in message
> news:bd9pnm$2lq h$1@msunews.cl. msu.edu...[color=green]
> > This may be a simple question to answer, but I don't have a clue since[/color][/color]
I'm[color=blue][color=green]
> > not a dba.
> >
> > I am using sql server 2000, while a client of ours is using sql server[/color][/color]
7.[color=blue][color=green]
> > Both using Windows 2000 as an OS, but I don't think that means anything
> > here.
> >
> > My question is, why does he have to specify the dbowner in his query
> > statements, while I don't? Is this a setting somewhere in Enterprise
> > Manager?
> >
> > For example, I can simply use the query "select * from tablename" while[/color][/color]
he[color=blue][color=green]
> > must use something like "select * from sa.tablename".
> >
> > Any help would be appreciated.
> >
> > Thanks,
> > Frank
> >
> >[/color]
>
>[/color]