Permissions to AD groups

Posted on 2012-09-10
Last Modified: 2012-09-14

I have a problem issuing permissions to groups

I have created three different AD groups which includes several distribution groups
created in windows


I have created three different schemas and want these groups to have different permissions on each of these schema

schemas names created  

sql_data_Admin group needs to have full control(select,update,delete ,insert,create,drop ,
create view,stored procs and functions rights) to dbo schema and only grant read access to cure and easy  schema

sql_data_reader group needs to have read access over dbo and cure schema and full  
                                                            control (select,update,delete ,insert,create,drop ,
create view,stored procs and functions rights) to easy schema  

sql_data_writer group needs to have read access over dbo and easy schema and full  
                                                            control (select,update,delete ,insert,create,drop ,
create view,stored procs and functions rights) to cure schema  

How i set this up for each group is by giving below rights

grant control on schema::dbo to sql_data_Admin
grant select to schema::easy on sql_data_Admin
grant select on schema::cure to sql_data_Admin

grant control on schema::easy to sql_data_reader
grant select to schema::dbo to sql_data_reader
grant select to schema::cure to sql_data_reader

grant control to schema::cure to sql_data_writer
grant select to schema::dbo to sql_data_writer
grant select to schema::easy to sql_data_writer

Does a control access rights allow to perform ddl and dml operations on schema or
do i need to give individual rights.
is this the right way to do it.

Please advise
Question by:isonali
    LVL 9

    Expert Comment

    The control permission should give you alter permission on the schema and that will allow DDL. There is a potential security issue if you grant alter schema in databases that have cross_db_ownership_chaining on and the different schemas are owned by the same principal.

    Author Comment

    How about creating view, stored proc and functions, will grant control permission on schema to the group allow users to create those as well?
    LVL 9

    Accepted Solution

    Within the schema - yes

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now