Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 707
  • Last Modified:

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?
0
yanci1179
Asked:
yanci1179
  • 3
1 Solution
 
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
 
chapmandewCommented:
geez....wrong again.  try this:

deny 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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now