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

Database Paging Options with 2M Rows

After seaching a few resources and seeing a few ideas for database paging
(e.g. returning a subset of result to a web app based upon some paging
algorithm on the web, I was wondering if anyone has used paging effectively
on a result set as large as 2 M rows? I have currently built a table with
all the columns possible for the web app we are using; built a clustered
index the most used field (e.g. date_received desc order); build nonclustered
indices on the other columns that could be sorted by in the app; and finally
a procedure that dynamically (using 'exec (' select ...') builds the query.
Where I see a performance issue with this type of sql is when I have a large
result set (50K rows or so) and need to get to page 100. Any immediate
thoughts that come to mind?
http://www.aspfaq.com/2120
http://www.aspfaq.com/
(Reverse address to reply.)
"Jay Schirmacher" <JaySchirmacher@.discussions.microsoft.com> wrote in
message news:53C30568-F9E1-4418-8E66-B0FE840608E4@.microsoft.com...
> After seaching a few resources and seeing a few ideas for database paging
> (e.g. returning a subset of result to a web app based upon some paging
> algorithm on the web, I was wondering if anyone has used paging
effectively
> on a result set as large as 2 M rows? I have currently built a table
with
> all the columns possible for the web app we are using; built a clustered
> index the most used field (e.g. date_received desc order); build
nonclustered
> indices on the other columns that could be sorted by in the app; and
finally
> a procedure that dynamically (using 'exec (' select ...') builds the
query.
> Where I see a performance issue with this type of sql is when I have a
large
> result set (50K rows or so) and need to get to page 100. Any immediate
> thoughts that come to mind?
|||I did see your previous post with this link. I will test it out later and
reply with the repsonses. My situation is slightly different (as one would
expect) in that most of the query will need to be dynamically built.
More later and thanks for the reply.
"Aaron [SQL Server MVP]" wrote:

> http://www.aspfaq.com/2120
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Jay Schirmacher" <JaySchirmacher@.discussions.microsoft.com> wrote in
> message news:53C30568-F9E1-4418-8E66-B0FE840608E4@.microsoft.com...
> effectively
> with
> nonclustered
> finally
> query.
> large
>
>

Database Paging Options with 2M Rows

After seaching a few resources and seeing a few ideas for database paging
(e.g. returning a subset of result to a web app based upon some paging
algorithm on the web, I was wondering if anyone has used paging effectively
on a result set as large as 2 M rows? I have currently built a table with
all the columns possible for the web app we are using; built a clustered
index the most used field (e.g. date_received desc order); build nonclustere
d
indices on the other columns that could be sorted by in the app; and finally
a procedure that dynamically (using 'exec (' select ...') builds the query.
Where I see a performance issue with this type of sql is when I have a large
result set (50K rows or so) and need to get to page 100. Any immediate
thoughts that come to mind?http://www.aspfaq.com/2120
http://www.aspfaq.com/
(Reverse address to reply.)
"Jay Schirmacher" <JaySchirmacher@.discussions.microsoft.com> wrote in
message news:53C30568-F9E1-4418-8E66-B0FE840608E4@.microsoft.com...
> After seaching a few resources and seeing a few ideas for database paging
> (e.g. returning a subset of result to a web app based upon some paging
> algorithm on the web, I was wondering if anyone has used paging
effectively
> on a result set as large as 2 M rows? I have currently built a table
with
> all the columns possible for the web app we are using; built a clustered
> index the most used field (e.g. date_received desc order); build
nonclustered
> indices on the other columns that could be sorted by in the app; and
finally
> a procedure that dynamically (using 'exec (' select ...') builds the
query.
> Where I see a performance issue with this type of sql is when I have a
large
> result set (50K rows or so) and need to get to page 100. Any immediate
> thoughts that come to mind?|||I did see your previous post with this link. I will test it out later and
reply with the repsonses. My situation is slightly different (as one would
expect) in that most of the query will need to be dynamically built.
More later and thanks for the reply.
"Aaron [SQL Server MVP]" wrote:

> http://www.aspfaq.com/2120
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Jay Schirmacher" <JaySchirmacher@.discussions.microsoft.com> wrote in
> message news:53C30568-F9E1-4418-8E66-B0FE840608E4@.microsoft.com...
> effectively
> with
> nonclustered
> finally
> query.
> large
>
>

Database Paging Options with 2M Rows

After seaching a few resources and seeing a few ideas for database paging
(e.g. returning a subset of result to a web app based upon some paging
algorithm on the web, I was wondering if anyone has used paging effectively
on a result set as large as 2 M rows? I have currently built a table with
all the columns possible for the web app we are using; built a clustered
index the most used field (e.g. date_received desc order); build nonclustered
indices on the other columns that could be sorted by in the app; and finally
a procedure that dynamically (using 'exec (' select ...') builds the query.
Where I see a performance issue with this type of sql is when I have a large
result set (50K rows or so) and need to get to page 100. Any immediate
thoughts that come to mind?http://www.aspfaq.com/2120
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Jay Schirmacher" <JaySchirmacher@.discussions.microsoft.com> wrote in
message news:53C30568-F9E1-4418-8E66-B0FE840608E4@.microsoft.com...
> After seaching a few resources and seeing a few ideas for database paging
> (e.g. returning a subset of result to a web app based upon some paging
> algorithm on the web, I was wondering if anyone has used paging
effectively
> on a result set as large as 2 M rows? I have currently built a table
with
> all the columns possible for the web app we are using; built a clustered
> index the most used field (e.g. date_received desc order); build
nonclustered
> indices on the other columns that could be sorted by in the app; and
finally
> a procedure that dynamically (using 'exec (' select ...') builds the
query.
> Where I see a performance issue with this type of sql is when I have a
large
> result set (50K rows or so) and need to get to page 100. Any immediate
> thoughts that come to mind?|||I did see your previous post with this link. I will test it out later and
reply with the repsonses. My situation is slightly different (as one would
expect) in that most of the query will need to be dynamically built.
More later and thanks for the reply.
"Aaron [SQL Server MVP]" wrote:
> http://www.aspfaq.com/2120
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Jay Schirmacher" <JaySchirmacher@.discussions.microsoft.com> wrote in
> message news:53C30568-F9E1-4418-8E66-B0FE840608E4@.microsoft.com...
> > After seaching a few resources and seeing a few ideas for database paging
> > (e.g. returning a subset of result to a web app based upon some paging
> > algorithm on the web, I was wondering if anyone has used paging
> effectively
> > on a result set as large as 2 M rows? I have currently built a table
> with
> > all the columns possible for the web app we are using; built a clustered
> > index the most used field (e.g. date_received desc order); build
> nonclustered
> > indices on the other columns that could be sorted by in the app; and
> finally
> > a procedure that dynamically (using 'exec (' select ...') builds the
> query.
> > Where I see a performance issue with this type of sql is when I have a
> large
> > result set (50K rows or so) and need to get to page 100. Any immediate
> > thoughts that come to mind?
>
>