|
Home > Archive > MS SQL Server > March 2006 > what user security allows just Stored Procs?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
what user security allows just Stored Procs?
|
|
|
| In 2005:
What SQL Security options will allow the execution of only stored procs?
The sprocs do SELECTs, UPDATEs, DELETEs and INSERTs.
When I click on the security node inside the database, and then right click
a user to see properties, there are "Schemas owned by this user" (such as
db_datareader and db_datawriter), and then there is another category,
"database role membership", which also includes things such as db_datareader
and db_datawriter. What's the difference and what should I check to allow
use the sprocs to be executed?
| |
|
| YOU can define a database toel on your own for just executing stored
procedures. Furthon you could define a DDL trigger on the database
which adds the execute right automatically to the SP when it is
created.
HTH; Jens Suessmeyer.
---
http://www.sqlserver2005.de
---
| |
| David Portas 2006-03-25, 3:23 am |
| HK wrote:
> In 2005:
>
> What SQL Security options will allow the execution of only stored procs?
> The sprocs do SELECTs, UPDATEs, DELETEs and INSERTs.
>
> When I click on the security node inside the database, and then right click
> a user to see properties, there are "Schemas owned by this user" (such as
> db_datareader and db_datawriter), and then there is another category,
> "database role membership", which also includes things such as db_datareader
> and db_datawriter. What's the difference and what should I check to allow
> use the sprocs to be executed?
You need to grant EXECUTE permission directly on procs and then deny
all permissions on other objects. Take a look at GRANT in Books Online.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
| |
|
| Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:428321
I found this article very helpful, and it also mentions why it is not
necessary to grant execute permissions at the SP scope in 2005 because you
can do it simply at the whole database scope.
http://www.sqldbatips.com/showarticle.asp?ID=8
"HK" < replywithingroup@not
real.com> wrote in message
news:G33Vf.11908$w86.9150@tornado.socal.rr.com...
> In 2005:
>
> What SQL Security options will allow the execution of only stored procs?
> The sprocs do SELECTs, UPDATEs, DELETEs and INSERTs.
>
> When I click on the security node inside the database, and then right
click
> a user to see properties, there are "Schemas owned by this user" (such as
> db_datareader and db_datawriter), and then there is another category,
> "database role membership", which also includes things such as
db_datareader
> and db_datawriter. What's the difference and what should I check to
allow
> use the sprocs to be executed?
>
>
|
|
|
|
|