GRANT VIEW DEFINITION to script out tables

21 December,2016 by Jack Vamvas

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)

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on GRANT VIEW DEFINITION to script out tables


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer