How to Grant limited permissions to create views in another schema?

How to Grant limited permissions to create views in another schema?

In SQL Server 2005 and 2008R2 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. 

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 yourdb;
GO

USE yourdb;
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 yourdb;
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

Error Msg 262, level 14, state 1, line 2

Create table permission denied in database ‘yourdb’

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

Error Msg 2760, level 16, state 1, procedure Aview, line2

The Specified schema name dbo either does not exist or you don’t have permissions

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 Fails: Error msg create table permission denied in database ‘yourdb’

Create View is Now Successful