grant access to extended properties

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

Any ideas anyone? Cheers!

BenBen (benblo@.gmail.com) writes:

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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:

Quote:

Originally Posted by

Ben (benblo@.gmail.com) writes:

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Ben (benblo@.gmail.com) writes:

Quote:

Originally Posted by

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

EXECUTE AS is a new feature for SQL 2005.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx