Users cannot view tables in non-default schema in SSMS

Question

I'm having an issue setting the VIEW DEFINITION permission appropriately at the schema level for one of my users. I've created the schema TestSchema and added some tables. The user currently has permissions set to access & modify the table (SELECT, UPDATE, DELETE, etc) through the dbo_datareader and dbo_datawriter roles. However, they cannot see any of the tables in the SSMS object explorer.

I've tried granting permissions to view definitions:

grant view definition on SCHEMA :: [TestSchema] to [User]

That didn't work. I tried setting the table-level permission:

grant view definition on [TestSchema].[NewTable] to [User]

That also didn't work. Then I tried just a blanket grant:

grant view definition to [User]

And that did work; they can now see TestSchema, as well as other schemas that they shouldn't have access to.

My goal here is to allow the user to view all tables within a given schema. How do I accomplish that? If I should be able to do this by default, what permissions should I be looking at to find why I can't?

Answer 1

Short answer: Don't use db_datareader or db_datawriter or their deny equivalents. They are for backwards compatibility only. Using them will cause issues like the one you are facing.

If you want to give principal Alice the SELECT, INSERT, UPDATE and DELETE permissions to all table-valued objects in schema Sales then use the following.

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::Sales TO Alice ;

If you want to give principal Alice the SELECT, INSERT, UPDATE and DELETE permissions to all table-valued objects in all schemas then use the following.

GRANT SELECT, INSERT, UPDATE, DELETE TO Alice ;

Metadata visibility will then work correctly.

Answer 2

Take a look at the principals other permissions there might be a deny in there somewhere that is is causing the problem. Remember a deny will always apply over the top of a grant. And as its not your servr there might somthing in the account that the IT dept have set up.

Use somthing like this for server level:

    use master
    go
    SELECT a.*,b.name as 'Login ID' FROM sys.server_permissions as A inner join 
    sys.server_principals as B on a.grantee_principal_id = b.principal_id
    where b.name = 'login ID of the account'
    GO

And somthing like this at database level

SELECT a.*,b.name as 'Login ID' FROM sys.database_permissions as A 
inner join sys.server_principals as B on a.grantee_principal_id = b.principal_id
where b.name = 'test'
GO

Answer 3

This happened to me when I upgraded our SQL Server from 2008R2 to 2014. db_datareader or db_datawriter or their deny equivalents perfectly work fine in 2008R2 but they doesn't work in 2014.

Example to grant permission in SQL Server 2014

If you want to give user Alice the SELECT, INSERT, UPDATE and DELETE permissions to all table-valued objects in schema Sales then use the following.

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::Sales TO Alice ; If you want to give principal Alice the SELECT, INSERT, UPDATE and DELETE permissions to all table-valued objects in all schemas then use the following.

GRANT SELECT, INSERT, UPDATE, DELETE TO Alice ; Metadata visibility will then work correctly.

Reference [Greenstone Walker].