Post reply

User can't see definition of view even with db_owner permissions

  • February 3, 2021 at 9:08 pm

    #3840689

    Hello experts,

    We have a user with an AD account who reported that he cannot see the definition of a view. That account (call it MYAD\user1) is in group MYAD\AllowDDL.

    The user keeps saying he gets the error below.

    Property TextHeader is not available for View. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted. (Microsoft.SqlServer.Smo)

    The view is not encrypted. I checked with this query:

    select object_name(id), encrypted, * from syscomments where encrypted <> 0

    MYAD\AllowDDL had db_ddladmin, so I figured that includes viewing definitions. Perhaps someone can confirm if that is the case.

    Same error, MYAD\user1 said. So I explicitly granted VIEW DEFINITION to MYAD\AllowDDL.

    Same error, MYAD\user1 said. So I added MYAD\AllowDDL to db_owner.

    Same error, MYAD\user1 said.

    I ran this from my SSMS window:

    execute as user='MYAD\user1'

    select current_user

    SELECT TABLE_NAME as ViewName,
    VIEW_DEFINITION as ViewDefinition
    FROM INFORMATION_SCHEMA.Views
    where TABLE_NAME='the_view'

    I can see the view definition in the output. I asked the user to run the 'SELECT TABLE_NAME as ViewName, ...' query and he says the definition is NULL.

    This is driving me crazy. Can anyone shed light on what is going on? If this is user error, how can I figure that out? It seems from the screenshots that he is sending that he indeed cannot see the view.

    Thanks for any help.

    -- webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • February 3, 2021 at 11:17 pm

    #3840722

    Can other users see the definition?

    I had a similar issue long ago.  The problem was the comments at the top of the view definition.

    I think, and it's been a while, that there was comments inside of comments.

    I queries the sys.syscomments table, and recreated the code from that.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • February 4, 2021 at 3:01 pm

    #3840990

    Hi Michael,

    Thanks for your feedback. I will look into the comments issue. I can see the view definition, but I am sysadmin so that makes sense. I will test with another non-sysadmin user to check.

    Thanks again.

    -- webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

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

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