May 15, 2006 at 10:30 am
Why would a user in the db_ddladmin role be able to execute any ALTER or CREATE statement they wanted, but not be able to use the Modify Table function in SSMS object browser? If I manually grant Control rights on a table to the user then SSMS allows modification, but otherwise it displays the grid with the column properties as read-only.
This only happens on our SQL 2005 test server, this has always worked in either SSMS or EM on our SQL 2000 servers.
May 15, 2006 at 1:06 pm
After some experimentation I found that
grant
VIEW DEFINITION on SCHEMA::dbo to ddladmin_user
lets user modify tables in SSMS as expected. I'm still confused about why it would be set up this way, and whether this is the minimum-rights action to take. Without taking this step SSMS shows them the table-definition grid as read-only, so they are obviously able to view the table definition. But it won't let them make any changes even though they can execute ALTER, CREATE, and DROP commands in scripts.
In SQL 2000 someone needed EXECUTE rights on a set of system stored procs to be able to create diagrams. Is the command above working because it gives them rights to some system object required by the SSMS Modify Table function, rather than any change in the permissions of the user tables themselves?
February 10, 2012 at 9:46 am
Well done Scott. This has been doing my head-in.
We have a user with his own domain account & granted the db_ddladmin role in a database. He can alter objects in scripts but not with the GUI. It simply complains that he is not the owner (dbo.TableName), which we know. His default schema is dbo.
I know there is an issue with Windows Groups and Default Schemas (i.e. not possible) but this is an issue for individual accounts too.
You can't grant this privilege to the db_ddladmin db role directly (grant VIEW DEFINITION on SCHEMA::dbo to db_ddladmin). I guess because it should be implied. As it doesn't work as expected possibly a bug, therefore.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply