Problem: Users are unable to create views in a database even after being granted the CREATE VIEW permission. How can this issue be resolved?

Solution: In SQL Server 2005 and 2008, permissions can be granted at the schema level. By granting the necessary permissions at the schema level, users will be able to create views without encountering permission errors.

First, let’s set up an example scenario:

CREATE DATABASE MSSQLTips;
GO

USE MSSQLTips;
GO

CREATE ROLE LimitedCreatorRights;
GO

GRANT CREATE VIEW TO LimitedCreatorRights;
GO

GRANT SELECT ON SCHEMA::dbo TO LimitedCreatorRights;
GO

CREATE USER TestUser WITHOUT LOGIN;
GO

EXEC sp_addrolemember 'LimitedCreatorRights', 'TestUser';
GO

CREATE TABLE dbo.ATest (TestID INT);
GO

In the above example, we have created a database, a role called “LimitedCreatorRights”, and a user called “TestUser”. The “LimitedCreatorRights” role has been granted the CREATE VIEW permission and SELECT permission on the dbo schema. The TestUser has been added as a member of the LimitedCreatorRights role.

However, the TestUser does not have permission to create tables. Let’s see what happens when the TestUser tries to create a table:

USE MSSQLTips;
GO

-- This will fail, as TestUser doesn't have CREATE TABLE permissions
EXECUTE AS USER = 'TestUser';
GO

CREATE TABLE dbo.ASecondTable (TestID INT);
GO

REVERT;
GO

As expected, the TestUser is unable to create a table in the dbo schema.

Similarly, if the TestUser tries to create a view, it will also fail:

-- This will fail, as TestUser does have CREATE VIEW rights
-- but does not have permission to alter the dbo schema
EXECUTE AS USER = 'TestUser';
GO

CREATE VIEW dbo.AView AS SELECT TestID FROM dbo.ATest;
GO

REVERT;
GO

The TestUser does have the CREATE VIEW permission, but it lacks permission to alter the dbo schema, which is required to create views.

To solve this issue, we need to grant the ALTER permission on the dbo schema to the LimitedCreatorRights role:

-- Once permission is granted, re-run the previous CREATE VIEW
-- statement. It will now succeed.
GRANT ALTER ON SCHEMA::dbo TO LimitedCreatorRights;
GO

Now, if we re-run the CREATE VIEW statement, it will succeed:

EXECUTE AS USER = 'TestUser';
GO

CREATE VIEW dbo.AView AS SELECT TestID FROM dbo.ATest;
GO

REVERT;
GO

However, granting ALTER permission on the dbo schema also allows the TestUser to alter and drop objects within that schema, including tables. This may not be desirable.

To address this issue, a DDL trigger can be used to intercept ALTER TABLE and DROP TABLE events. This will be covered in a follow-on tip.

In conclusion, by granting CREATE VIEW permission at the schema level and ensuring the necessary ALTER permission on the schema, users can create views without encountering permission errors. However, caution should be exercised to prevent unintended modifications or deletions of objects within the schema.