When working with SQL Server 2008 security, a common challenge emerges when trying to grant CREATE TABLE permissions at the schema level rather than database-wide. The fundamental issue stems from how SQL Server implements these permissions hierarchically.

Here's the key technical detail many DBAs miss:

-- This grants database-wide permission
GRANT CREATE TABLE TO [username]

Versus what we actually want:

-- Attempting schema-specific control
GRANT ALTER ON SCHEMA::[schemaname] TO [username]

In SQL Server 2008, CREATE TABLE is indeed a database-level permission. The schema-specific permissions like ALTER or CONTROL don't implicitly grant the right to create objects - they only permit modification of existing objects within that schema.

To achieve schema-limited table creation, you need a two-pronged approach:

-- First grant the database-level permission
GRANT CREATE TABLE TO [username]

-- Then restrict to specific schema via ownership
ALTER AUTHORIZATION ON SCHEMA::[schemaname] TO [username]

This combination effectively limits the user's table creation to the specified schema while maintaining proper security boundaries.

For more complex scenarios, consider creating a database role:

-- Create the role
CREATE ROLE [SchemaTableCreator]

-- Grant necessary permissions
GRANT CREATE TABLE TO [SchemaTableCreator]
GRANT ALTER ON SCHEMA::[schemaname] TO [SchemaTableCreator]

-- Add user to role
EXEC sp_addrolemember 'SchemaTableCreator', 'username'

Be aware that granting CREATE TABLE permission, even when schema-limited, still allows users to:

• Create tables with potentially poor design
• Consume database resources
• Create objects that might conflict with naming conventions

Always combine these permissions with proper naming conventions and potentially DDL triggers for additional control.

After implementing, verify the permissions work as intended:

-- Execute as the test user
CREATE TABLE [schemaname].[TestTable] (ID INT)
-- Should succeed

CREATE TABLE [otherschema].[TestTable] (ID INT)
-- Should fail with permission error

In SQL Server 2008, the CREATE TABLE permission is indeed a database-level permission by default. This means when you grant CREATE TABLE to a user, they can create tables anywhere in the database. However, there are ways to restrict this to specific schemas through a combination of permissions.

To allow table creation in a specific schema while preventing it elsewhere, you need both:

-- Grant ALTER permission on the specific schema
USE [YourDatabase]
GRANT ALTER ON SCHEMA::[SchemaName] TO [UserName]
GO

-- Grant CREATE TABLE at database level
USE [YourDatabase]
GRANT CREATE TABLE TO [UserName]
GO

The ALTER permission on the schema allows the user to modify the schema's contents, while CREATE TABLE at the database level enables the actual table creation capability. Without both, the operation will fail.

For better permission management, consider creating a database role:

-- Create a role for schema-specific access
USE [YourDatabase]
CREATE ROLE [SchemaCreatorRole]
GO

-- Grant permissions to the role
GRANT ALTER ON SCHEMA::[SchemaName] TO [SchemaCreatorRole]
GRANT CREATE TABLE TO [SchemaCreatorRole]
GO

-- Add user to the role
EXEC sp_addrolemember 'SchemaCreatorRole', 'UserName'
GO

To check effective permissions, use:

-- Check effective permissions
USE [YourDatabase]
EXECUTE AS USER = 'UserName'
SELECT HAS_PERMS_BY_NAME(NULL, 'DATABASE', 'CREATE TABLE')
SELECT HAS_PERMS_BY_NAME('SchemaName', 'SCHEMA', 'ALTER')
REVERT
GO

Remember that ALTER permission on a schema also allows other modifications. For tighter control, you might need to implement DDL triggers or use Certificate-based signing for specific operations.