Re: grant access to extended properties

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

.

Relevant Pages

  • RE: exec sp_help_job user account rights
    ... Execute permissions default to the public role in the msdb database. ... who can execute this procedure and is a member of the sysadmin fixed role can ... the SQL Server service is running. ... impersonation and sp_help_job is always executed under the security context ...
    (microsoft.public.sqlserver.security)
  • Re: Run Jobs in Enterprise Manager
    ... Execute permissions default to the public role in the msdb database. ... A user who is not a member of the sysadmin role can use ... sp_start_job will impersonate the SQL Server ... > I have several DTS packages in Enterprise Manager that are run as jobs in SQL Server Agent. ...
    (microsoft.public.sqlserver.dts)
  • Re: Output stored procedure
    ... but the .execute command doen't show teh records. ... What object window? ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: grant access to extended properties
    ... I played around with GRANT but apparently, a member of "db_datareader" ... Reading Books Online tells us that to add extended properties, ... and then add WITH EXECUTE AS proxyuser, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: the security needed for SET IDENTITY_INSERT
    ... the procedure on db2 first insert data to another Users table (in old ... EXECUTE AS is not good for many reasons. ... Did you read the permissons section in Books Online? ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.programming)