Database Roles

In SQL Server 2005, I want to create a database role and then create
additional roles based on the earlier role (sort of an inheritance approach
to creating roles). However, I can't seem to find a way to make one role a
member of another, share schema... Is this possible or advisable?
Michael HocksteinHi Michael,
Thank you for using MSDN Managed Newsgroup Support.
From your description, my understanding of this issue is: You want to
assign a database role to be a member of another database role. If I
misunderstood your concern, please feel free to let me know.
You can not assign a database role to be a member of another database role.
If you want to grant the same permission of another database role and
extend the permission, please grant the CONTROL permission to the database
role for your additional database role.
Thank you for taking the time to provide feedback on this product.
We are very interested in your thoughts and opinions for improvements that
we can make to provide the features and functionality you and your
customers would like to see.
To provide your feedback, please go the following website:
http://lab.msdn.microsoft.com/produ...ck/default.aspx
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Database Roles
>thread-index: AcZtT6Kc1Lw4anhJSDuWBEEKvOfeXA==
>X-WBNR-Posting-Host: 198.133.139.5
>From: examnotes <howlinghound@.nospam.nospam>
>Subject: Database Roles
>Date: Mon, 1 May 2006 11:47:02 -0700
>Lines: 7
>Message-ID: <DB15940E-88EE-44C8-9D9F-7023C5356760@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>Newsgroups: microsoft.public.sqlserver.security
>Path: TK2MSFTNGXA01.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.security:27213
>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>X-Tomcat-NG: microsoft.public.sqlserver.security
>In SQL Server 2005, I want to create a database role and then create
>additional roles based on the earlier role (sort of an inheritance
approach
>to creating roles). However, I can't seem to find a way to make one role a
>member of another, share schema... Is this possible or advisable?
>--
>Michael Hockstein
>|||So, if I execute a statement such as:
GRANT CONTROL ON Role1 TO Role2
Go
then anything that Role1 had permissions to would be applied to Role2 as wel
l?
Michael Hockstein
"Wei Lu" wrote:

> Hi Michael,
> Thank you for using MSDN Managed Newsgroup Support.
> From your description, my understanding of this issue is: You want to
> assign a database role to be a member of another database role. If I
> misunderstood your concern, please feel free to let me know.
> You can not assign a database role to be a member of another database role
.
> If you want to grant the same permission of another database role and
> extend the permission, please grant the CONTROL permission to the database
> role for your additional database role.
> Thank you for taking the time to provide feedback on this product.
> We are very interested in your thoughts and opinions for improvements that
> we can make to provide the features and functionality you and your
> customers would like to see.
> To provide your feedback, please go the following website:
> http://lab.msdn.microsoft.com/produ...ck/default.aspx
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> --
> approach
>|||Hi Michael,
As I mentioned in the previous post, you can not grant the same permission
of a database role directly.
My suggestion is, you could check the sys.database_permissions catalog view
to see what the permissions does the original role grant and you could
grant the additional role the same permissions.
Grant the control permission to a database role will not grant the same
permission on database.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||"michael" <howlinghound@.nospam.nospam> wrote in message
news:DB15940E-88EE-44C8-9D9F-7023C5356760@.microsoft.com...
> In SQL Server 2005, I want to create a database role and then create
> additional roles based on the earlier role (sort of an inheritance
> approach
> to creating roles). However, I can't seem to find a way to make one role a
> member of another, share schema... Is this possible or advisable?
>
Not possible, but:
With the ability to grant on the database or schema level, the number of
indvidual grants necessary to implement security is typically much reduced.
You can add users to multiple roles, so the incremental rights can be
attached to an additional role.
David