K. Brian Kelley (5/12/2011)
Since CCR does not own the dbo schema, you must give permission to ALTER it, too.
GRANT ALTER ON SCHEMA::dbo TO ccr;
However, you probably want to create a role, given the permissions to the role, and make ccr a member of the role. For instance:
CREATE ROLE ModifyTable;
GO
GRANT CREATE TABLE TO ModifyTable;
GRANT ALTER ON SCHEMA::dbo TO ModifyTable;
GO
EXEC sp_addrolemember 'ModifyTable', 'ccr';
GO
REVOKE CREATE TABLE FROM ccr;
Now, because a user has ALTER permissions on the schema, he/she can affect existing objects. So you'll have to build a DDL trigger to restrict the role to just being able to touch tables. There are examples of this in the forums and the scripts if you do a search.
Here's some code for the DDL trigger referenced above:
-- This trigger fires on all DDL database level events (https://technet.microsoft.com/en-US/library/ms191441(v=SQL.90).aspx)
-- If the login is a member of the 'ModifyTable' role,
-- execute their DDL statement only if it is one of the following SQL statements:
--1. CREATE TABLE
--2. DROP TABLE
--3. ALTER TABLE
--4. SELECT INTO
CREATE TRIGGER db_trigger_BlockNonTableDDL
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
IF IS_MEMBER('ModifyTable') = 1
BEGIN
DECLARE @TriggerEventText nvarchar(max);
SET @TriggerEventText = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
IF NOT ((@TriggerEventText LIKE 'CREATE TABLE%') OR
(@TriggerEventText LIKE 'DROP TABLE%') OR
(@TriggerEventText LIKE 'ALTER TABLE%') OR
(@TriggerEventText LIKE 'SELECT % INTO %'))
BEGIN
RAISERROR (@TriggerEventText, 16, 1)
ROLLBACK TRANSACTION;
END
END;
END;
GO