object_id(what) .. returns diffrent value depending on user

Question

Post reply

object_id(what) .. returns diffrent value depending on user

Edward-445599

Hall of Fame

Points: 3147
More actions
August 23, 2011 at 3:50 am

#242507

hi,
If I login as the schema user and excute
SELECT OBJECT_NAME(id) FROM syscolumns WHERE id=object_id('mytable')
I get a value returned.
If I then log on as SA and run the command against the same DB nothing is returned.
Why is ths?
Many thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Change the database 🙂

Most likely, when you logon as "same user"? you are connected to your database by default.

When you logon as SA, by default you are connected to master database.

So, try what is said in the first line :hehe:

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

Could also be a different schema. Try qualifying the object with the schema name.

-- Gianluca Sartori

Edward-445599 Hall of Fame Points: 3147 More actions · Answer 3

Eugene Elutin (8/23/2011)
Change the database 🙂
Most likely, when you logon as "same user"? you are connected to your database by default.
When you logon as SA, by default you are connected to master database.
So, try what is said in the first line :hehe:

Please see my orginal post

"If I then log on as SA and run the command against the same DB nothing is returned"

Edward-445599 Hall of Fame Points: 3147 More actions · Answer 4

Gianluca Sartori (8/23/2011)
Could also be a different schema. Try qualifying the object with the schema name.

Yep thanks 🙂

Lowell SSC Guru Points: 323518 More actions · Answer 5

you should be able to find what the issue is pretty easily;

if you change the object id below to the one you are looking for, what do you get?

on my server with 77 dev databases, i got 67 matches, some were PK,some tables , one was tempdb.sys.service_broker_map.

exec sp_msForEachDB '

select

''?'' as DbName,

schema_name(schema_id),

name,

object_id

from ?.sys.objects

where object_id = 2073058421 '

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!