INF: You may not be able to change the Design of a Table (Insert or Delete a Column) using GUI.

We recently worked on an issue where modifying a Table from the GUI was not allowed even though the account had read – write permissions.  So, we are sharing our findings below:

Let’s say you have a SQL account ‘Account_1’ with following Database roles:  DB_DataReader, DB_DataWriter and DB_DDLAdmin on database ‘Test_DB’ and have connected to SQL server with this account. When you try to modify the Table using T-SQL commands (DDL); you are able to do it. However, when you try to perform the same task using the GUI (i.e. SQL Server Management Studio from object explore); delete and insert column options are greyed out (as shown below).

For analyzing this situation, we performed the following steps:

We connected to the server using the SQL server Management studio with the credentials of account ‘Account_1’ andtried to access the same Database ‘Test_DB’ using the the object explorer. (GUI)

Note: We can access only those Databases for which we have access permissions.

Then we created a New Table; to which we received the following warnings however it was possible to create a table.

If we try to add a column or change the design of the table, it is not allowed,  and we receive the following Warnings.

Even after this, we click OK and move forward; it will display the structure of the table. However it will grey out all the alterations options like following:

Next, we went ahead to collect the profiler trace while performing the T-SQL commands to add a column as well as while accessing table using GUI. Following are the key records from both the Profilers.

Profiler for T-SQL command to Add a column:

It shows a smooth execution of the ALTER statement.

Profiler for GUI to Add a column:

It shows that, a check (mentioned below) is performed when we try to access the DESIGN of the table using the account with which we are connected, in our case  ‘Account_1’ .

Query:

 SELECT Has_perms_by_name(N'dbo.test_tab', 'Object', 'ALTER')           AS        alt_per, 
       Has_perms_by_name(N'dbo.test_tab', 'Object', 'VIEW DEFINITION') AS        view_def_per, 
       Has_perms_by_name(N'dbo.test_tab', 'Object', 'CONTROL')         AS        contr_per  

If you execute the above query in a New Query window and got the following output.

Output:

From the results we see, the Alter Permission was there but View Definition and Control permissions were not set; and this is the reason why the DESIGN or DELETE or INSERT column options are greyed out.

Explanation:

While altering the table from Query window, we just perform the alter command. Internally it will give a call to SYS.OBJECT and other related DMVs however SQL Server is not expected to display all this information.

On the other hand, when we try to alter the table from the GUI, we are intending to View the design of the table first and then make the required changes. For this the SELECT permission on the Metadata is not granted to the user. This is where the VIEW DEFINITION permission comes into the picture. SQL server checks if the account has Alter Permission, View Definition and Control permissions. As we just want to view the metadata, VIEW DEFINITION permissions are enough.

Hence granting the VIEW DEFINITION permission to the user allows the user to access the metadata and alter it.

An excerpt from the MSDN article: https://msdn.microsoft.com/en-us/library/ms175808.aspx

The VIEW DEFINITION permission lets a user see the metadata of the securable on which the permission is granted. However, VIEW DEFINITION permission does not confer access to the securable itself. For example, a user that is granted only VIEW DEFINITION permission on a table can see metadata related to the table in the sys.objects catalog view. However, without additional permissions such as SELECT or CONTROL, the user cannot read data from the table. When we use GUI we first access the metadata for the table and that's why check for the above three permissions. View Definition allows us to see the metadata. After checking this permission and since Alter was already granted, it allowed us to change the schema of the table.

Written By:

Shraddha Gupta - Support Engineer,Microsoft GTSC

Reviewed By:

Pradipta Das - Technical Lead, Microsoft GTSC

Sumit Sarabhai - Technical Lead, Microsoft GTSC