468,291 Members | 1,655 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,291 developers. It's quick & easy.

SQL Server 7 vs 2000 question

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
Jul 20 '05 #1
2 5215
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.MyTable

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.MyTable

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.Table), or dbo-owned
objects (dbo.Table), depending on who created them and what permissions you
have.

Simon

"Maze" <fl*****@configsc.com> wrote in message
news:bd***********@msunews.cl.msu.edu...
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

Jul 20 '05 #2
Thank you Simon, very helpful....

Frank

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:3e********@news.bluewin.ch...
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.MyTable

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.MyTable

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.Table), or dbo-owned
objects (dbo.Table), depending on who created them and what permissions you have.

Simon

"Maze" <fl*****@configsc.com> wrote in message
news:bd***********@msunews.cl.msu.edu...
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


Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Brad Tilley | last post: by
3 posts views Thread by datapro01 | last post: by
14 posts views Thread by Developer | last post: by
reply views Thread by Teichintx | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.