CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2008-r2
mehdi lotfi
mehdi lotfi

Reputation: 11571

SQL Server Extended property Permission

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.

Upvotes: 9

Views: 5208

Answers (2)

Milica Medic Kiralj
Milica Medic Kiralj

Reputation: 3750

It doesn't matter if you use a tool or not, to be able to add the descriptions the user needs to have modify permissions to the database (check your tool's documentation):

EXEC rights to the system stored procedures sp_addextendedproperty, fn_listextendedproperties, and sp_dropextendedproperty.

From msft site:

For: sp_addextendedproperty

Members of the db_owner and db_ddladmin fixed database roles can add extended properties to any object with the following exception: db_ddladmin cannot add properties to the database itself, or to users or roles. Users can add extended properties to objects they own or have ALTER or CONTROL permissions on.

For: sp_dropextendedproperty

Members of the db_owner and db_ddladmin fixed database roles may drop extended properties of any object. Users may drop extended properties to objects they own. However, only db_owner may drop properties to user names.

Hope this helps

Upvotes: 1

steoleary
steoleary

Reputation: 9278

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;

Upvotes: 6

Related Questions