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: db_denydatawriter
    ... change data in EM grid view, then closing the table & reopening it to see ... She is a member of sysadmin. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.security)