sql server user restrict to one schema

I have a sql login that i want to restrict to one schema, how can i do that?

for example user1
and
marketing.customers
accounting.customers

I want user1 to only see marketing.customers.  I have many more under the same schema.  I want the user to me able to select from all the tables that exists for this schema.  Is there a script for this?
yanci1179Asked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
geez....wrong again.  try this:

deny select on schema::accounting to username
0
 
chapmandewCommented:
you can grant the user view definition to the schema you want them to be able to see objects

grant view definition on schema::marketing to username

and to select data

grant select on schema::marketing to username

to resetrict to the other schema

deny select on schema::marketing to username
0
 
chapmandewCommented:
sorry last one should be:

grant select on schema::accounting to username
0
 
BrandonGalderisiCommented:
grant has a schema option here's the details.

http://msdn.microsoft.com/en-us/library/ms187940.aspx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.