Is There A Simple Way To Drop All Permissions Of A Role

There has to be a simple way to remove all the permissions of a given
database role without generating a pagefull of messages. REVOKE ALL... will
do the job but outputs redundant messages telling you what the ALL really
means. Is there a more appropriate way of removing permissions (short of
DROP'ing the role outright - which would first entail dropping all the
members)
--
Michael HocksteinHi Michael,
My understanding of your issue is that:
You want to find a simple way to remove all permissions of a given database
role without generating a page full of messages. You don't want to use
REVOKE ALL because of its producing redundant messages.
If I have misunderstood, please let me know.
As far as I know, REVOKE ALL is the simplest way to remove all permissions
on a role. For the warning, I am afraid that this is an issue by design. I
recommend that you give Microsoft feedback on this issue which will be
routed to SQL Team so that this issue can be improved in the next release.
I recommend that you give Microsoft feedback via:
http://connect.microsoft.com
Your feedback will be routed to SQL team so that this issue can be resolved
in the next release.
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Thanks for your insight. No problem using REVOKE ALL, even with the messages
.
Michael Hockstein
"Charles Wang[MSFT]" wrote:

> Hi Michael,
> My understanding of your issue is that:
> You want to find a simple way to remove all permissions of a given databas
e
> role without generating a page full of messages. You don't want to use
> REVOKE ALL because of its producing redundant messages.
> If I have misunderstood, please let me know.
> As far as I know, REVOKE ALL is the simplest way to remove all permissions
> on a role. For the warning, I am afraid that this is an issue by design. I
> recommend that you give Microsoft feedback on this issue which will be
> routed to SQL Team so that this issue can be improved in the next release.
> I recommend that you give Microsoft feedback via:
> http://connect.microsoft.com
> Your feedback will be routed to SQL team so that this issue can be resolve
d
> in the next release.
> If you have any other questions or concerns, please feel free to let me
> know. It's my pleasure to be of assistance.
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>
>|||Hi Michael,
Thank you very much for your understanding.
I had thought that if you were very concerned with the warning, you could
write a program and use SMO library to enumerate the permissions on an
user, and then revoke all the permissions one by one. However I noticed
that you wanted a simple way, so I thought the warning may be not your much
concerned issue.
If you have any other questions or concerns, please feel free to let me
know. It's always my pleasure to be of assistance.
Have a great day!
Charles Wang
Microsoft Online Community Support

Is there a script to return permissions for an SP?

I am trying to define through a sub-thousand SPs what users have rights to
run it. I want to see if there are open doors that I'm not aware of.
TIA
__StephenHi,
See SP_HELPROTECT system stored procedure in books online. As well as you
can query sysprotects table.
Thanks
Hari
SQL Server MVP
"Stephen Russell" <srussell@.transactiongraphics.com> wrote in message
news:%23USy01ZrFHA.1172@.TK2MSFTNGP11.phx.gbl...
>I am trying to define through a sub-thousand SPs what users have rights to
>run it. I want to see if there are open doors that I'm not aware of.
> TIA
> __Stephen
>|||"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O$rupmcrFHA.2880@.TK2MSFTNGP12.phx.gbl...
> Hi,
> See SP_HELPROTECT system stored procedure in books online. As well as you
> can query sysprotects table.
Perfect. Thanks allot