Hidden Table

[dnfrantum]

I am working with an Application that has purposely hidden a table from view in Query Analyzer. Is there a way to expose that table through permissions? I am logged on as sa and I am working on MS SQL 2005 SE

Thanks in advance,
Donald

 

[JohnBates]

Donald,

Are you saying that you cannot access the table even when logged on as sa? sa has full permissions and can "see" all objects.

I don't know what you mean by the term "hidden table".

John

 

[dnfrantum]

Correct. Even logged in as sa, I can't see the table. I didn't know it was possible to programatically hide a table, but that is what the application forum is indicating. I wanted to know if there was a way around it.

Thanks in advance,
Donald

 

[gmmastros]

Is it possible that the application is actually working with a VIEW?

What do you get if you run this in a query window?

Select * 
From   Information_Schema.Tables 
Where  Table_Name = 'YourHiddenTableName'

There is a table_Type column returned. If it's really a table, it will say "Base Table". If it's a view, it will say view.

If it's a view, you can see what the definition of the view is by running....

sp_helptext 'ViewNameHere'



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dnfrantum]

It didn't show as any of the above. Is is possible that the table name is being aliased in the application and that the real table name is something different? Is there a way to search the database for specific data and return where it is located?

Thanks in advance,
Donald

 

[gmmastros]

Maybe it's a synonym. Synonyms are new to SQL2005. Try running this in a query window.

Select * From sys.synonyms

If you want to search the tables for certain values, you should expect the search to be slow (especially for a large database).

http://blogs.lessthandot.com/index..../the-ten-most-asked-sql-server-questions--1#2

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mrdenny]

Try to query the sys.all_objects catalog view. This will have a list of every object in the database and the type of object it is.

SELECT *
FROM sys.all_objects
WHERE name = 'YourHiddenTable'

As has been said before, there is no way to hide an object from the sa account (or any member of the sysadmin fixed server role for that matter).

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[dnfrantum]

Is it possible that it could be aliased in the application? I don't see it in the database using any of the techniqes provided, but it must exist. The table is essentially all of the authorizations for the users, but the application author doesn't want to easily expose that information. I am fairly certain it is in the database, but the table name provided by the application doesn't exist anywhere in application or system databases.

Thanks in advance,
Donald

 

[mrdenny]

It is possible that what the application is calling the table isn't actually the table name. The developer could have hidden the data that way.

Setup SQL Profiler then login to the application. This will show you what commands are being against the database so you can find the table.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog