Error on Extended properties when creating Objects that are not in the dbo schema

Question

Error on Extended properties when creating Objects that are not in the dbo schema

Welsh Corgi

SSC Guru

Points: 116520
More actions
March 1, 2012 at 11:57 am

#265376

I have a developer that gets the following error when trying to create a table that is not in the dbo schema:
Msg 15135, Level 16, State 1, Procedure sp_addextendedproperty, Line 37 Object is invalid. Extended properties are not permitted on 'HisSchema.Accounts_Payable.Line_Item_Number' or the object does not exist.
What permissions do I need to grant him?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

The following error occurs when trying to create extended properties on a schema other than dbo.

Msg 15135, Level 16, State 1, Procedure sp_addextendedproperty, Line 37 Object is invalid. Extended properties are not permitted on 'HisSchema.Accounts_Payable.Payable_Type_Code', or the object does not exist.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

zymos SSCommitted Points: 1960 More actions · Answer 2

Hi,

It seems that only the db_owner or the db_ddladmin may be able to achieve what you need to achieve per my understanding of your question. See below URL and it's explanations and I hope that helps.

URL: http://msdn.microsoft.com/en-us/library/Aa259572

Regards,

Zymos.

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 3

Did you check the following issues?

What is the default schema this user has?

What permission does the user have on this schema?

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

He had a systax error:

The Developers syntax was incorrect.:w00t:

This worked.

EXEC sys.sp_addextendedproperty

@name = N'Welsh',

@value = N'Minimum inventory quantity.',

@level0type = N'SCHEMA', @level0name = HisSchema,

@level1type = N'TABLE', @level1name = Accounts_Payable,

@level2type = N'COLUMN', @level2name = Payable_Type_Code;

GO

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/