locked
Can't script out table constraints RRS feed

  • Question

  • I have a new database and the users and db_datareader and db_datawriter Roles.  When the developers try to script out table constraints they get a permission issue.  Does anyone now what privilege I should GRANT to they can see and script out the default constraints on a table?  We are using SQL Server 2005 Enterprise.

    Is there a permission I can set on the schema so all new talbes will inherit the permission?

    Thanks in advance.
    Thursday, November 5, 2009 2:59 PM

Answers

  • AS Tibor says use that grant view definition for both the public role at the server level and user at the database level.

    USE MASTER
    GO
    GRANT VIEW ANY DEFINITION TO PUBLIC
    GO
    USE DBNAME
    GO
    GRANT VIEW DEFINITION TO USERNAME
    GO
    Thanks, Leks
    • Marked as answer by Fred_1000 Thursday, November 5, 2009 8:54 PM
    Thursday, November 5, 2009 4:08 PM

All replies

  • Possibly GRANT VIEW DEFINITION ... will help you.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Thursday, November 5, 2009 3:28 PM
  • AS Tibor says use that grant view definition for both the public role at the server level and user at the database level.

    USE MASTER
    GO
    GRANT VIEW ANY DEFINITION TO PUBLIC
    GO
    USE DBNAME
    GO
    GRANT VIEW DEFINITION TO USERNAME
    GO
    Thanks, Leks
    • Marked as answer by Fred_1000 Thursday, November 5, 2009 8:54 PM
    Thursday, November 5, 2009 4:08 PM
  • Thanks, granting view definition to the public schema worked great.  I had granted view definition to the table but the table constratins still weren't scriptable. 

    Thursday, November 5, 2009 8:56 PM
  • Hi Tibor,

    Thanks for the reply.  I have learned a lot from your website and your blogging over the years.

    Thanks.
    Thursday, November 5, 2009 9:13 PM
  • Cool. :-)

    Note that granting to public might be overkill, might be better to grant only to those who need it.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Friday, November 6, 2009 6:54 AM