MS SQL Server - SQL Server not recognizing tables that a user owns.

Author

SQL Server not recognizing tables that a user owns.

Chris

2005-10-31, 9:23 am

SQL Server 2000 SP3

One of our client has an issue where if they log in as a SQL user in say
Query Analyzer and try to access a table that they own it will return an
error (unknown object etc).

eg
Select * From TableName

However if they prefix the table with their user name (even though they own
it)

eg
Select * From UserName.TableName

It will return rows.

This was working until mid afternoon today. However the they assure me that
appart from usual user usage of the databases that no one has been playing
around.

They also had some old copied of the database on that server which no one
had accessed for over a month and that also had the same issue.

They tried retoring the database with out success.

We have also tried to get them to run the following.

sp_change_users_logi
n 'Update_One', 'username', 'username'

to no avaial.

I have asked them to restore a copy of their masterdb. I am still awaiting
the results on this.

Does anyone have any other ideas on what to try.

Thanks
Chris

Dan Guzman

2005-10-31, 9:23 am

Perhaps the user in question was added to the sysadmin role. In that case,
the user's security context is 'dbo' instead of the expected user. Try the
following when logged in as the user:

SELECT USER

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Chris" <Chris@discussions.microsoft.com> wrote in message
news:FE9BDEB5-469F-4C64-8788- 1477D4376768@microso
ft.com...
> SQL Server 2000 SP3
>
> One of our client has an issue where if they log in as a SQL user in say
> Query Analyzer and try to access a table that they own it will return an
> error (unknown object etc).
>
> eg
> Select * From TableName
>
> However if they prefix the table with their user name (even though they
> own
> it)
>
> eg
> Select * From UserName.TableName
>
> It will return rows.
>
> This was working until mid afternoon today. However the they assure me
> that
> appart from usual user usage of the databases that no one has been playing
> around.
>
> They also had some old copied of the database on that server which no one
> had accessed for over a month and that also had the same issue.
>
> They tried retoring the database with out success.
>
> We have also tried to get them to run the following.
>
> sp_change_users_logi
n 'Update_One', 'username', 'username'
>
> to no avaial.
>
> I have asked them to restore a copy of their masterdb. I am still awaiting
> the results on this.
>
> Does anyone have any other ideas on what to try.
>
>
> Thanks
> Chris

MarkusB

2005-10-31, 9:23 am

Chris,

that's totally normal behaviour. If the object owner in a SQL 2000
database is different from dbo, you must use the two-part name
ownername.objectname

M

Narayana Vyas Kondreddi

2005-10-31, 9:23 am

It looks like your user has been added to the sysadmin fixed server role or
db_owner database role. In which case, SQL Server is looking for objects
owned by dbo, not the user's old login name.

What's the output of the following cammand, when your user runs it from
Query Analyzer?

SELECT USER_NAME()

--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/

"Chris" <Chris@discussions.microsoft.com> wrote in message
news:FE9BDEB5-469F-4C64-8788- 1477D4376768@microso
ft.com...
SQL Server 2000 SP3

One of our client has an issue where if they log in as a SQL user in say
Query Analyzer and try to access a table that they own it will return an
error (unknown object etc).

eg
Select * From TableName

However if they prefix the table with their user name (even though they own
it)

eg
Select * From UserName.TableName

It will return rows.

This was working until mid afternoon today. However the they assure me that
appart from usual user usage of the databases that no one has been playing
around.

They also had some old copied of the database on that server which no one
had accessed for over a month and that also had the same issue.

They tried retoring the database with out success.

We have also tried to get them to run the following.

sp_change_users_logi
n 'Update_One', 'username', 'username'

to no avaial.

I have asked them to restore a copy of their masterdb. I am still awaiting
the results on this.

Does anyone have any other ideas on what to try.

Thanks
Chris

Kalen Delaney

2005-10-31, 11:23 am

Actually, not quite.
If the owner is the current user, the two-part name is not necessary.

When an object is specified without an owner name, SQL Server first checks
to see if the current user is the owner of an object with the given name,
and then checks to see if dbo is the owner of an object with the given name.
If neither the current user or dbo owns an object with the given name, it
generates an 'object not found' error.

My guess is that the current user has a different user name than expected.
This can happen when someone is made a member of the sysadmin role in which
case they assume the name dbo in all databases.

--
HTH
Kalen Delaney, SQL Server MVP
www. solidqualitylearning
.com

"MarkusB" <m.bohse@quest-consultants.com> wrote in message
news:1130769065.871105.129320@o13g2000cwo.googlegroups.com...
> Chris,
>
> that's totally normal behaviour. If the object owner in a SQL 2000
> database is different from dbo, you must use the two-part name
> ownername.objectname
>
> M
>
>

Sponsored Links