February 8, 2010 at 2:10 pm
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.
February 10, 2010 at 2:24 pm
Just a question, Do you have any Deny VIEW DEFINITION or CONTROL permissions for that user?
-Roy
May 31, 2013 at 12:06 pm
I am experiencing this same issue with MS SQL 2008 R2 SP1 SQL, anyone find a resolution?
June 1, 2013 at 4:59 pm
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
June 17, 2013 at 4:04 am
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
June 17, 2013 at 12:27 pm
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
May 20, 2016 at 5:52 am
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.
May 20, 2016 at 6:51 am
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
May 20, 2016 at 7:44 am
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.
July 3, 2016 at 5:55 am
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.
July 3, 2016 at 4:42 pm
ok; I know this is really old post and has surely been resolved but just in case it's not...(as there was a new post)
Run SP_helprotect @username = 'whateverUserName' on the database where the login has the access and then again on the one where you do not. This will give you a definitive list of the permissions that user has on each db. Then check the the roles membership for each database.
If it works with dbo but not with db_datareader there has to be another permission either missing or being denied.
Hope this helps
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
July 19, 2016 at 1:52 pm
I would guess that the difference would be in the server level permissions for public and a difference between those for the two environments, especially for the permissions VIEW ANY DATABASE. That's just my guess though, could be other things but I have seen this permission causing similar issues, confusions.
Sue
September 6, 2016 at 10:46 am
I'll provide more details hopefully soon, once I confirm; but starting to wonder if this issue is more related to the tool itself (Mgmt Studio) than permissions, etc.
For instance, I created a SQL user "readOnly" ... I have a user who can't see the tables on her machine; yet another user is able to see the tables just fine on their machine. Both users are using the same "readOnly" login. However, the users are not using the same version of Management Studio; in fact, one of them may be using SQLExpress.
Kris
September 6, 2016 at 11:23 am
By default, VIEW DEFINITION (access to DDL text of an object) is denied. You can grant this a user at the server, database, schema, or object level. The following article provides examples of this.
http://sql.richarddouglas.co.uk/archive/2010/05/grant-view-definition.html#axzz4JUtrFArE
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 6, 2016 at 11:43 am
Eric, Did you see my first post right before yours? How do you explain that? Very odd yes?
It's the same SQL account; one user can see the tables, the other user cannot. I don't think it's related to permissions, etc. I've never had to explicitly set permissions for a public user to merely see tables, especially when they have the db_datareader permissions.
Kris
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply