Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2016
    Posts
    4

    Question Unanswered: Granting privileges to a user to one database schema and deny to others schema

    Currently i have this situation:

    Database ZEN with schemas A, B, C, D, E.
    User test;

    I have to give user test db_reader, db_writer and ddl_admin access on all of databases A and B objects (tables, views, etc), so this user can select, update, delete, insert, create table, drop table, alter table.

    How can i do this the best way?

    Actually iīm trying to do this by using the Studio, going on the properties of the user -> securables -> search -> adding all objects belonging to schema C, D and E; One by one i must mark deny on everything on all these schema objects.

    I hope someone can tell me an easiest way to do this.

    Thank you.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You just need a mix of schema level permissions and database level permissions.

    Code:
     -- Note:  Objects can only be added to schemas the user has ALTER permissions on
    grant create table to test
    grant create view to test
    grant create procedure to test
    
    grant alter on schema ::A to test
    grant alter on schema ::B to test
    grant select, insert, update, delete on schema ::A to test
    grant select, insert, update, delete on schema ::B to test

  3. #3
    Join Date
    Jun 2016
    Posts
    4
    Quote Originally Posted by MCrowley View Post
    You just need a mix of schema level permissions and database level permissions.

    Code:
     -- Note:  Objects can only be added to schemas the user has ALTER permissions on
    grant create table to test
    grant create view to test
    grant create procedure to test
    
    grant alter on schema ::A to test
    grant alter on schema ::B to test
    grant select, insert, update, delete on schema ::A to test
    grant select, insert, update, delete on schema ::B to test
    Thank you very much. Perfect.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •