Can't script out table constraints

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.

Answer 1

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

Answer 2

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

Answer 3

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. 

Answer 4

Hi Tibor,

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

Thanks.

Answer 5

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