Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
On my 2008R2 instance when I grant db_datareader or db_datawriter they can see all schemas. I do realize that's not what you're looking for but it means there's a significant difference between your setup and mine so I can't really test it. Is the version all that's different or has some other security been modified? –  cfradenburg Oct 25 '12 at 12:49
    
@cfradenburg - It's difficult to tell; this is a server managed by IT, so I'm not sure what they did, and I'm a SQL Server novice, so I don't know what to look for. I guess part of my question is, what other permissions/roles/??? should I look for to determine if this can be simply fixed by permissions? –  eykanal Oct 25 '12 at 15:08
    
Can the user query the tables? If they can query then it could just be an issue within SSMS. –  user9164 Oct 25 '12 at 21:57
    
@user9164 Yes, the users can query the tables; it's just an issue of viewing them within SSMS. –  eykanal Oct 25 '12 at 23:40
add comment

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
add comment

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
share|improve this answer
    
Thanks for the comment, I'll try this out when I get back to work on Monday. –  eykanal Nov 25 '12 at 14:39
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.