We all know that the Management Console has sometimes its own logic ...
Well, if you are working with database roles and want to give a user only the db_DDLAdmin role, this user will not be able to change the structure of a table using the Management Console.
Glad to say, that with T-Sql you will be able :-)
This behaviour is remarked in at least Sql Server 2005 SP1 and SP2 (9.0.3054)
Look how you can REPRODUCE this behaviour (in your test environment of course):
1. Execute the following batch:
| | USE Master
CREATE DATABASE Test
CREATE LOGIN [test] WITH PASSWORD=N'T1st', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
db_
USE Test
CREATE USER [test] FOR LOGIN [test] WITH DEFAULT_SCHEMA=[dbo]
EXEC sp_AddRoleMember 'db_DDLAdmin', 'Test' |
2. Open the Object Explorer with the login "Test", expanding database "Test"
3. Create a new table
| | you get the message |
| |  |
| | Press <OK> and continue |
4. Define some fields and save
| | you get the warning |
| |  |
| | Press <Yes> and the table is saved. |
5. Select the table in the Object Explorer and R*, choose "Design"
| | this message we know already |
| | |
| | Press <OK> and ...
up the next messagebox |
| |  |
| | Press <OK> and ...
indeed you are not able to change anything in this table because the GUI is read-only. |
SOLUTION
You can solve this by granting the permission "view definition" for this user
| | USE Test
EXEC sp_AddRoleMember 'db_DDLAdmin', 'Test' |
When this user tries to change the design of the table, (s)he will still get the "Validation Warnings" message (see above, press <Yes>) and the new structure will be saved.
Do realise that you give an extra permission, but I suppose that in most situations, if you are allowed to create, alter and delete any object, you are also allowed to view its definition.
Tip: search ones in BOL after "VIEW DEFINITION Permission" and you will discover some other nice roles, e.g. the server role "VIEW ANY DATABASE" permission, which is granted to the public role by default (as consequence, by default, every user that connects to a Sql 2005 can see all the databases on that instance).