Post reply

Property Text is not available for DefaultConstraint

  • April 14, 2017 at 2:53 am

    #322962

    Hello.

    Please,can you help me.

    When I try open with right click (Create) table type from mangment studio i get error

    Property Text is not available for DefaultConstraint.....This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

    I gave VIEW definition rights to user, but error occured.
    SQL Servser 2012 Ent ed
    Please, help me, What rights do i need to give?

    Sory for my English((

  • April 14, 2017 at 3:55 pm

    #1938239

    I'm not sure about this but, for a user to see object info they would need VIEW DEFINITION rights at the DB, Schema and Object level. Does this help? 

    GRANT VIEW DEFINITION ON DATABASE :: <database> TO <user>;
    GRANT VIEW DEFINITION ON SCHEMA :: <schema> TO <user>;
    GRANT VIEW DEFINITION ON OBJECT :: <table> TO <user>;

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • April 14, 2017 at 4:20 pm

    #1938240

    I've seen that before but have no idea how we fixed it. The one thing I do remember is that the user did get different results when they would query sys.default_constraints. No errors just different results. The only constraints that showed up are the ones for the table where the user had view definition. And if they were given view definitions to a table without any default constraints, they could script out the create table without any problems or any errors. Worked fine.
    I just can't remember what the constraint definition was that caused the error. You could also try just having the user do that query against sys.default_constraints and make sure they get back all the constraints for the table you are having the problems with. One of the constraints for the table may not show up in the list of constraints if you do something like:

    select name, definition, OBJECT_NAME(parent_object_id) as TableName
    from sys.default_constraints

    I just remember it was something with that default constraint that caused the error. I would guess Alan's suggestion should work - I'd probably try the schema first, then the database. But I still keep thinking it was some reference in the constraint itself.
    Please post back your results - I just tried some tests to figure it out but didn't have any success. And now it's one of those things where I wish I could remember the specific permissions that caused that error. Maybe it was a function in the constraint? One of those that's driving me crazy right now since I can't remember.

    Sue

Viewing 3 posts - 1 through 2 (of 2 total)

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