Re: Granting EXEC for read only SPs
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Mon, 05 May 2008 15:23:33 -0700
BGL (BGL@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
Is there any way to grant EXEC on all SPs to a user or role where the SPs
don't modify user tables?
The only way to achieve exactly that is to grant access per procedure.
I created database role 'X' and granted EXEC permission for all SPs.
Then, I created a database user, added it to the role 'X' to inherit the
EXEC permissions, AND ALSO added the user to the fixed database role
'denydatawriter'.
This is because of ownership chaining. The rights of the procedure
owner applies, and not those of the current user. This works as long
as the procedure and the accessed tables has the same owner. Thus, if
you want the rights of the current user to apply, you can change the
owner of the procedure (or the tables) so that they are different.
The user will still be able to execute the procedures, but when the
procedures try to update, he will get a permission error.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- Prev by Date: Re: SQL2005 non-administrator Service Account
- Next by Date: Alter User for domain user
- Previous by thread: Network Service account authenticate itself as [NT Authority\Network Service]
- Next by thread: Alter User for domain user
- Index(es):
Relevant Pages
|
