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: 456
  • Last Modified:

Permissions to AD groups

Hi,

I have a problem issuing permissions to groups

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

sql_data_admin:
sql_data_reader
sql_data_writer

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

schemas names created  
dbo
easy
cure

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
0
Sonali P
Asked:
Sonali P
  • 2
1 Solution
 
Matt BowlerDB team leadCommented:
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.

http://msdn.microsoft.com/en-us/library/ms187940.aspx
0
 
Sonali PDatabase AdministratorAuthor Commented:
How about creating view, stored proc and functions, will grant control permission on schema to the group allow users to create those as well?
0
 
Matt BowlerDB team leadCommented:
Within the schema - yes
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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