|
|
Posted by Erland Sommarskog on 08/28/06 22:03
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
Navigation:
[Reply to this message]
|