Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

User (db_datareader) can't see tables in MS; can SELECT data! Expand / Collapse
Author
Message
Posted Monday, February 8, 2010 2:10 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, May 8, 2016 12:28 AM
Points: 885, Visits: 909
We have an Acitve Directory user that is assigned db_datareader for a given database. Given any table name in the database the user can successfully issue (select * from tablename). BUT no tables or views appear for that user under Management Studio. He can expand the database but no tables appear.

I note the user has "dbo" as their default schema and the database does NOT contain that schema (i.e. all tables were created under specific named-schemas, not dbo). I even tried changing his default schema to one of the named ones but to no avail (he still can't list or enumerate the tables or views in this database.)

Any ideas what to check for next?

TIA,

Barkingdog

BTW: It is permission related. As a test I assigned the user to the db_dbowner role and he can see all tables and views.



Post #862063
Posted Wednesday, February 10, 2010 2:24 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, March 8, 2016 7:47 AM
Points: 2,381, Visits: 6,838
Just a question, Do you have any Deny VIEW DEFINITION or CONTROL permissions for that user?

-Roy
Post #863637
Posted Friday, May 31, 2013 12:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 14, 2013 8:11 AM
Points: 1, Visits: 2
I am experiencing this same issue with MS SQL 2008 R2 SP1 SQL, anyone find a resolution?
Post #1458827
Posted Saturday, June 1, 2013 4:59 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, July 22, 2016 12:02 PM
Points: 7,917, Visits: 14,274
When logged in as this user, what do you get when you run:

SELECT  *
FROM sys.tables;



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1458975
Posted Monday, June 17, 2013 4:04 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 21, 2016 1:23 AM
Points: 193, Visits: 952
Roy gave the answer. This is expected behaviour if the login is not granted view definition

SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
Post #1464053
Posted Monday, June 17, 2013 12:27 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, July 22, 2016 12:02 PM
Points: 7,917, Visits: 14,274
Ness (6/17/2013)
Roy gave the answer. This is expected behaviour if the login is not granted view definition

Not exactly. Revoke (i.e. not being granted or denied a permission) is different than being explicitly denied a permission. If you have select permissions on a table you can see the metadata as well so the expectation is that if you are granted db_datareader then you should be able to see the metadata for all tables.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1464314
Posted Friday, May 20, 2016 5:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 298, Visits: 1,096
Hi, I appreciate this is a pretty old thread but did you ever get to the bottom of this? I'm experiencing the same issue - a user is a member of the db_datareader role and is able to run select queries against the tables in the database with no issue. However, they are unable to view the list of tables in Object Explorer. They can however see the table list in Object Explorer Details.

The instance is still on SQL 2008R2 RTM so I'm hoping getting the Service Packs applied will fix this but any information you found would be appreciated.
Post #1788019
Posted Friday, May 20, 2016 6:51 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 21, 2016 1:23 AM
Points: 193, Visits: 952
See above:
Roy gave the answer. This is expected behaviour if the login is not granted view definition


SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
Post #1788034
Posted Friday, May 20, 2016 7:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 298, Visits: 1,096
Thanks Ness but I don't think that solves my problem. The user has the exact same permissions (member of db_datareader only) on another database on the same instance and is able to view the table list in Object Explorer.

Even when I grant them VIEW DEFINITION on the database in question they are still unable to view the table list.
Post #1788049
Posted Sunday, July 3, 2016 5:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:39 AM
Points: 125, Visits: 790
SilentMike (5/20/2016)
Thanks Ness but I don't think that solves my problem. The user has the exact same permissions (member of db_datareader only) on another database on the same instance and is able to view the table list in Object Explorer.

Even when I grant them VIEW DEFINITION on the database in question they are still unable to view the table list.


I believe VIEW DEFINITION must go against MSDB.

You could also just give db_datareader access to MSDB but that may be too much access. Ive never found an issue doing this but some people are of the mind "Only give them what they need and not a bit more" where security is concerned.
Post #1799263
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse