0

Question:

By default a user can change their own extended properties. Is the following code appropriate to prevent a user changing their user extended properties attributes?:

USE [CustomerAccessDatabase];
GO
DENY SELECT ON sys.extended_properties to [Customer_Access_Role];
GO
USE [master];
GO
DENY EXEC ON sys.sp_addextendedproperty to [public];
GO
DENY EXEC ON sys.sp_dropextendedproperty to [public];
GO
DENY EXEC ON sys.sp_updateextendedproperty to [public];
GO
Max xaM
  • 101
  • 3

1 Answers1

0

I am reasonably confident that I have solved this problem and secured this sufficiently.

Denying select on sys.extended_properties is not an option, as this prevents selecting on all tables.

However, denying Exec on the system stored procedures is sufficient on its own. The user can see extended properties but cannot change them. Attempting to change extended properties via the front end of SSMS also fails since the back end calls the same system stored procedures.

Max xaM
  • 101
  • 3
  • If anyone has a way to deny extended property edits on a specific object (e.g. the User) then I will change my accepted answer. – Max xaM Mar 15 '16 at 22:48