Post reply

access syscomments table

  • July 9, 2009 at 10:13 am

    #208105

    i want to give priviledge to USER to accesss syscomments table so that he can see the script of views from TOAD. THe USER has db_datareader on the database. . If i give USER db_owner, USER can select syscomments with data on it. But i can't want to give him db_owner. I created the role that have SELECT on syscomments and assign to USER.However this role is just letting him select the table without data on it.

    what minimum permission i have to assign so that USER can access syscomments system table with data on it?

    Thanks

  • July 9, 2009 at 10:17 am

    #1022524

    syscomments isn't a table any longer. You should be using sys.sql_modules now.

    I believe you need to grant VIEW DEFINITION rights to the objects in the database. If everything's in the dbo schema, then you can grant VIEW DEFINITION on the schema and that will affect all the objects.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 9, 2009 at 10:34 am

    #1022535

    yes it is sys.syscomments.

    It says" grant or revoke VIEW DEFINATION is not compatitable wiht the object(error:4606)

  • July 9, 2009 at 10:44 am

    #1022540

    Even though syscomments is available, it is not recommended to use it since it is there for backward compatibility only and will be removed from a future version.

    In fact you may be getting the error because you're trying to grant VIEW DEFINITION on an object that's out there for backward compatibility only.

    Like Gail recommends you should use the sys.sql_modules catalog view instead.

  • July 9, 2009 at 10:46 am

    #1022543

    ruchika thapa (7/9/2009)

    yes it is sys.syscomments.

    sys.syscomments is not a table any longer. It's a deprecated, backward compatability view. Use sys.sql_modules instead

    It says" grant or revoke VIEW DEFINATION is not compatitable wiht the object(error:4606)

    What are you trying to grant the permission on?

    You need to grant it at a schema level, or to every single view, function and procedure in the database. It's easier to do at the scheme level, especially if all the objects are in the dbo schema.

    Look in Books Online for the exact syntax for granting a permission on a schema. I can't recall it exactly.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 9, 2009 at 11:22 am

    #1022566

    sys.sql_modules does not work here because USER needs to load view script from TOAD and TOAD uses Sys.syscomments.

  • July 9, 2009 at 11:46 am

    #1022576

    The requirement to grant view definition remains the same. Both syscomments and sys.sql_modules are based off the same system tables. It's just that syscomments will go away in a future version of SQL.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 9, 2009 at 11:53 am

    #1022580

    Thanks, it worked for me.

Viewing 8 posts - 1 through 7 (of 7 total)

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