SQL Server Extended property Permission

Question

I use SQL Server 2008 R2 and want to have an user for database documentation.

This user can only add description to all database objects such as database, table, view and column.

Which permission I must assigned to this user?

I don't want assign db_owner or db_ddladmin role to that user.

Thanks in advance.

Answer 1

So, to add extended properties to an object, you need to either own the object or have the CONTROL or ALTER privilege on the object.

So you can't really limit a user with the built in permissions to just adding extended properties as to add an extended property on the database, you would have to grant owner, control or alter at that level to the user.

What you can do though is create your own stored procedure to execute the system stored procedure with the same parameters and set it to execute as owner, then just grant execute on that procedure to the user. That way they can't do anything in the database apart from run that procedure that just adds the extended properties.

I have tested the following and it does work:

CREATE PROCEDURE add_property
(
@name NVARCHAR(128) = NULL,
@value NVARCHAR(128) = NULL,
@level0type NVARCHAR(128) = NULL,
@level0name NVARCHAR(128) = NULL,
@level1type NVARCHAR(128) = NULL,
@level1name NVARCHAR(128) = NULL,
@level2type NVARCHAR(128) = NULL,
@level2name NVARCHAR(128) = NULL
)
WITH EXECUTE AS OWNER
AS
EXEC sp_addextendedproperty 
@name, 
@value,
@level0type, 
@level0name,
@level1type, 
@level1name,
@level2type,
@level2name;