Re: grant access to extended properties
- From: "Ben" <benblo@xxxxxxxxx>
- Date: 29 Aug 2006 02:59:52 -0700
Thanks a lot for the quick answer!
Too bad i'm not using 2005... i'll have ot make the switch someday!!
Do you know anything about this "EXECUTE AS" for 2000? I had a quick
look through the documentation but i'm afraid it doesn't exist...
Erland Sommarskog wrote:
Ben (benblo@xxxxxxxxx) writes:
I have a user on my database that has only "select" access
(db_datareader).
Problem is, I also want him to also be able to create/update extended
properties on tables or views, but without modifying the tables'
schema.
I played around with GRANT but apparently, a member of "db_datareader"
cannot create/modify extended properties on an object if he's not the
owner of this object. I tried making this user a member of
"db_datawriter", but it didn't work.
Nothing short of making him member of "db_ddladmin" worked... but then
this is too much, the user can now alter to delete tables: i DON'T want
that!
Reading Books Online tells us that to add extended properties, you
need to be at least db_ddladmin.
On SQL 2005, you write a wrapper on the system procedures in question,
and then add WITH EXECUTE AS proxyuser, where proxyuser is a loginless
user which have been given the necessary permissions. For more details
on EXECUTE AS, there is an article on my web site:
http://www.sommarskog.se/grantperm.html.
--
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
.
- Follow-Ups:
- Re: grant access to extended properties
- From: Erland Sommarskog
- Re: grant access to extended properties
- References:
- grant access to extended properties
- From: Ben
- Re: grant access to extended properties
- From: Erland Sommarskog
- grant access to extended properties
- Prev by Date: Re: minimun installation
- Next by Date: Re: minimun installation
- Previous by thread: Re: grant access to extended properties
- Next by thread: Re: grant access to extended properties
- Index(es):
Relevant Pages
|