GRANT VIEW DEFINITION to script out tables

A developer contacted me to explain how he was not able to script out tables from a database .   He was surprised because he had the  right on some databases within the SQL Server instance but not on other databases.

The first thing I asked him was to supply the results of

SELECT * FROM fn_my_permissions (NULL, 'DATABASE');

For more information on fn_my_permissions read How to check my SQL permissions (SQL Server DBA)

Immediately I could see the problem. The current security context of the user did not include the VIEW DEFINITION privilege, therefore the use was not able to view the table metadata details.

The VIEW DEFINITION privilege allows the current security context of user to view the table metadata. This is different from viewing the table data , which requires read privileges.

The simple fix is to grant VIEW DEFINITION permissions . For example:

GRANT VIEW DEFINITION to user

Before applying this change , double check with the organisation SQL Server Security Policy . It is not unusual to see restrictions on this sort of privilege , and also it may need to be applied via a Role .

Read More

How to Script database role permissions and securables (SQL ...

SQL Server - Find sql database users and Find database roles

List sql database roles (SQL Server DBA)