Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1632 > unrolled thread

Extended property permissions

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  Extended property permissions Marco <noreply@no.reply> - 2013-12-20 09:40 -0800
    Re: Extended property permissions Erland Sommarskog <esquel@sommarskog.se> - 2013-12-20 23:01 +0100
      Re: Extended property permissions Marco <noreply@no.reply> - 2013-12-20 14:26 -0800
        Re: Extended property permissions Erland Sommarskog <esquel@sommarskog.se> - 2013-12-21 00:01 +0100

#1632 — Extended property permissions

In my SQL Server database (2008 R2), I have several extended 
properties, one of which I want to give a specific user the 
ability to read and update it.  Does anyone know if it would be 
possible to do that with sp's or views?  I don't want the user 
to have r/w access to any of the other extended properties, 
just this one.  Thanks

[toc] | [next] | [standalone]

#1633

Marco (noreply@no.reply) writes:
> In my SQL Server database (2008 R2), I have several extended 
> properties, one of which I want to give a specific user the 
> ability to read and update it.  Does anyone know if it would be 
> possible to do that with sp's or views?  I don't want the user 
> to have r/w access to any of the other extended properties, 
> just this one.  Thanks
 
To modify the extended properties of an object, the user needs to 
have ALTER or CONTROL permission on the object.

(According to the topics for sp_addextendedproperty and 
sp_updateextendedproperty in Books Online.

-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

[toc] | [prev] | [next] | [standalone]

#1634

On 12/20/2013 2:01 PM, Erland Sommarskog wrote:
> Marco (noreply@no.reply) writes:
>> In my SQL Server database (2008 R2), I have several extended
>> properties, one of which I want to give a specific user the
>> ability to read and update it.  Does anyone know if it would be
>> possible to do that with sp's or views?  I don't want the user
>> to have r/w access to any of the other extended properties,
>> just this one.  Thanks
>
> To modify the extended properties of an object, the user needs to
> have ALTER or CONTROL permission on the object.
>
> (According to the topics for sp_addextendedproperty and
> sp_updateextendedproperty in Books Online.

Yes I know that, thanks.  But I'm wondering if there is a way, 
perhaps through a stored procedure or a view, to give a user 
read and update permissions to one specific extended property only.

[toc] | [prev] | [next] | [standalone]

#1635

Marco (noreply@no.reply) writes:
> Yes I know that, thanks.  But I'm wondering if there is a way, 
> perhaps through a stored procedure or a view, to give a user 
> read and update permissions to one specific extended property only.
> 

Yes, you could create a procedure that performs the operations you
want to expose. Then you create a certificate and sign that procedure 
with the certificate. Then you create a user from that certificate 
and grant that user ALTER on the object in question. (This user is 
not a real user that log in or anything.)

For a detailed discussion of this technique, please see this article
on my web site: http://www.sommarskog.se/grantperm.html


-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web