> 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!

Erland Sommarskog wrote:
> Ben (benblo@gmail.com) 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@sommarskog.se
>
> 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