[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 551
  • Last Modified:

what is permission needed to alter column description

In SQL Server Management Studio, I want to add a description to a table column, but I get the error: "Not the owner"

What permission to I need to give to this user to be able to alter the column properties (it's a bit odd since the user is able to drop and create the table, but not alter attributes!)
0
jmarkfoley
Asked:
jmarkfoley
3 Solutions
 
selva_konguCommented:
Give alter permission to your user name

GRANT ALTER ON <schema.your table> TO [<your database user>]

Reffer the MSDN link
http://msdn.microsoft.com/en-us/library/ms187965.aspx
0
 
keyuCommented:
agreed with selva.. for other ref use below link

http://technet.microsoft.com/en-us/library/ms188371.aspx
0
 
jmarkfoleyAuthor Commented:
That worked for udating descriptions. Can I make the priv database-wide or do I have to do this table-by-table? Is there a way to let a user do EVERYTHING to a table w/o being a dbo? E.g. setting the alter priv. worked for updating the descriptions (still with lots of dire warnings), and this user can also add a column (again with warnings about possible failure), and create indexes, but cannot move a column in Management Studio.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
DBAduck - Ben MillerPrincipal ConsultantCommented:
ALTER privileges on a table gives all privileges for modifying the table, including adding a column, etc.

Moving a column in Mgmt Studio is a problem because Management Studio will want to recreate the table and will not let anyone do it.  You have to do this manually now as it is considered a destructive operation.

When you say move a column are you talking about changing the order or columns in the table?  The only way to do that is to drop and recreate the table with the right order and copy the data out.  So you would copy the data out into another table (staging) and then drop and recreate the original table and put the indexes, etc. back and then copy the data out of the staging table into the new table.
0
 
jmarkfoleyAuthor Commented:
Yes, I do mean moving the order of columns. I can move a column if I am logged in as the owner. Not a big deal if non-owners can't do it. I'll just make sure I log in as owner for such things.

What about being able to give non-owners ALTER permission to modify descriptions, etc. globally for all the tables in a database without have to grant that table-by-table. Is that possible.
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
That may be more like the database role ddl_admin.  That is a pretty heavy role, but it would keep you from having to grant the permission to all the tables.
0
 
Anthony PerkinsCommented:
Yes, I do mean moving the order of columns. I can move a column if I am logged in as the owner.
Just a clarification and as pointed out previously.  There is no T-SQL command to "move a column".  You may think that is what is happening when using SSMS Designer, but in fact, in the background, you are creating a new table with the correct structure and copying the data to the new table.  In order to do this you have to have permissions to CREATE TABLE, DROP TABLE and rename.
0
 
jmarkfoleyAuthor Commented:
Thanks!
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now