Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

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: Monday, November 10, 2014 2:04 PM
Points: 865, Visits: 874
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: Friday, November 14, 2014 12:37 PM
Points: 2,361, Visits: 6,752
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: Yesterday @ 8:12 AM
Points: 7,135, Visits: 12,746
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: Tuesday, November 18, 2014 9:40 AM
Points: 179, Visits: 835
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: Yesterday @ 8:12 AM
Points: 7,135, Visits: 12,746
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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse