Grant limited permissions to create SQL Server views in another schema Part 1

By: K. Brian Kelley   |   Updated: 2009-07-20   |   Comments   |   Related: > Security

Problem

I have users who need to be able to create their own views for reporting in a database. However, if I give them the CREATE VIEW permission, they are still getting a permission error when creating the view. I want them to create all the views in a given schema, which matches that of the tables they are building views on. How do I solve this issue?

Solution

In SQL Server 2005 and 2008 you can grant permissions at the schema level and, in fact, this is what you'll need to do to give them the ability to create the views.

First, a bit of setup for an example.

This script below creates an example database along with a role to which we'll assign the permissions to. Note that while I'm using the dbo schema, that's only because there's no logical schema name to use since this isn't a real world example. Typically you would name your schema to group objects and the schema name should reflect what the grouping is. For instance, Person or Product. As can be seen from the example, the LimitedCreatorRights role has the ability to create views in the database and select on tables and views that are located in the dbo schema.

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

One thing we've not given is the permission to create tables. In the following examples you will see that I am using the EXECUTE AS and the REVERT commands. The EXECUTE AS allows you to still be logged in with sysadmin rights, but run these examples using the TestUser permissions and the REVERT returns permissions back to the original user.

So if a user that is a member of this role attempts to create a table in the dbo schema, it'll fail:

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 

And, in fact, so will the creation of a view:

-- 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 catch is that the TestUser must have the permission to modify the dbo schema. We can accomplish this by assigning that permission to a role the TestUser is a member of:

-- 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 you go back and re-run the CREATE TABLE and the CREATE VIEW statements above, you'll see the CREATE TABLE statement fails (we didn't give TestUser or any role it is a member of the permission to create a table), but the create view statement will succeed.

Create Table Still Fails

Create View is Now Successful

There is one issue with this set of permissions, however. Because the user has ALTER permissions on the dbo schema, the user can alter and even drop objects within that schema. This includes tables, which isn't obvious at first because usually you would think that without CREATE TABLE permissions, the user couldn't do anything with the tables. But in fact, both of the following examples will succeed if you attempt them:

EXECUTE AS USER = 'TestUser'; 
GO 

ALTER TABLE dbo.ATest 
ADD AnotherID INT NULL; 
GO 

REVERT; 
GO   
-- and this will work as well
EXECUTE AS USER = 'TestUser'; 
GO 

DROP TABLE dbo.ATest; 
GO 

REVERT; 
GO 

There is a work around for this, but it requires a DDL trigger which intercepts ALTER TABLE and DROP TABLE events. That will be covered in a follow-on tip.

Next Steps

• When granting permissions to users make sure you give them enough access to do their job, but don't give permissions that are not needed.
• Read more about the EXECUTE AS and the REVERT
• Read more about GRANT VIEW
• Download the scripts
• Read more security tips

About the author

K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips